package com.audi.box.adapter;

import java.util.ArrayList;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.audi.dto.BoxItem;
import com.audi.dto.DeviceItem;
import com.audi.dto.HistoryActionSmartTriggerDto;
import com.audi.dto.HistoryChangePlaceOfDevice;
import com.audi.dto.HistoryDeviceItemAddRemove;
import com.audi.dto.HistoryMemberActionJoin;
import com.audi.dto.HistoryMemberActionRemoved;
import com.audi.dto.HistoryMemberActionSignout;
import com.audi.dto.HistoryMemberItem;
import com.audi.dto.HistoryPlaceItem;
import com.audi.dto.HistoryPlaceItemAddRemove;
import com.audi.dto.HistoryRenamePlaceOrChangeCapability;
import com.audi.dto.HistorySceneItemAddRemove;
import com.audi.dto.HistoryWarningSensorsTrigger;
import com.audi.dto.HistoryWifimatchHome;
import com.audi.dto.MemberItem;
import com.audi.dto.PlaceItem;
import com.audi.dto.SceneItem;
import com.audi.dto.SmartTriggerItem;
import com.audi.dto.WifiItem;

public class SQLiteAsusNewHelper extends SQLiteOpenHelper {
    
    private static final String DATABASE_NAME = "audi.db";
    private static final int DATABASE_VERSION = 1;
    
    // TABLE DEVICE
    private static final String TABLE_DEVICE = "device";
    private static final String KEY_DEVICE_ID = "_device_id";
    private static final String KEY_DEVICE_NAME = "_device_name";
    private static final String KEY_DEVICE_ICON = "_device_icon";
    private static final String KEY_DEVICE_STATUS = "_device_status";
    private static final String KEY_DEVICE_TYPE = "_device_type";
    private static final String KEY_DEVICE_CONTROL_VALUE = "_device_control_value";
    private static final String KEY_DEVICE_CODE = "_device_code";
    private static final String KEY_DEVICE_BOX_ID = "_device_box_id";
    private static final String KEY_DEVICE_PLACE_ID = "_device_place_id";
    private static final String KEY_DEVICE_USER_ID = "_device_user_id";
    private static final String KEY_DEVICE_IS_DELETED = "_device_is_deleted";
    private static final String KEY_DEVICE_FAVORITE = "_device_favorite";
    private static final String CREATE_TABLE_DEVICE = "CREATE TABLE "
            + TABLE_DEVICE + "("
            
            + KEY_DEVICE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_DEVICE_NAME + " TEXT," + KEY_DEVICE_ICON + " TEXT,"
            + KEY_DEVICE_STATUS + " INTEGER," + KEY_DEVICE_TYPE + " INTEGER,"
            + KEY_DEVICE_CONTROL_VALUE + " INTEGER," + KEY_DEVICE_CODE
            + " TEXT," + KEY_DEVICE_BOX_ID + " INTEGER," + KEY_DEVICE_PLACE_ID
            + " INTEGER," + KEY_DEVICE_USER_ID + " INTEGER,"
            + KEY_DEVICE_IS_DELETED + " INTEGER DEFAULT 0 ,"
            + KEY_DEVICE_FAVORITE + " INTEGER  DEFAULT 0" + ")";
    
    // TABLE PLACE
    private static final String TABLE_PLACE = "place";
    private static final String KEY_PLACE_ID = "_place_id";
    private static final String KEY_PLACE_NAME = "_place_name";
    private static final String KEY_PLACE_ICON = "_place_icon";
    private static final String KEY_PLACE_CAPABILITY_TYPE = "_place_capability_type";
    private static final String KEY_PLACE_IS_DELETED = "_place_is_deleted";
    private static final String CREATE_TABLE_PLACE = "CREATE TABLE "
            + TABLE_PLACE + "(" + KEY_PLACE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_PLACE_NAME + " TEXT,"
            + KEY_PLACE_ICON + " TEXT," + KEY_PLACE_CAPABILITY_TYPE
            + " INTEGER," + KEY_PLACE_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE SCENE
    private static final String TABLE_SCENE = "scene";
    private static final String KEY_SCENE_ID = "_scene_id";
    private static final String KEY_SCENE_NAME = "_scene_name";
    private static final String KEY_SCENE_ICON = "_scene_icon";
    private static final String KEY_SCENE_COLOR = "_scene_color";
    private static final String KEY_SCENE_PLACE_ID = "_scene_place_id";
    private static final String KEY_SCENE_IS_DELETED = "_scene_is_deleted";
    private static final String CREATE_TABLE_SCENE = "CREATE TABLE "
            + TABLE_SCENE + "(" + KEY_SCENE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_SCENE_NAME + " TEXT,"
            + KEY_SCENE_ICON + " TEXT," + KEY_SCENE_COLOR + " INTEGER,"
            + KEY_SCENE_PLACE_ID + " INTEGER," + KEY_SCENE_IS_DELETED
            + " INTEGER DEFAULT 0" + ")";
    
    // TABLE SCENE_DEVICE
    private static final String TABLE_SCENE_DEVICE = "scene_device";
    private static final String KEY_SCENE_DEVICE_SCENE_ID = "_scene_device_scene_id";
    private static final String KEY_SCENE_DEVICE_DEVICE_ID = "_scene_device_device_id";
    private static final String KEY_SCENE_DEVICE_DEVICE_STATUS = "_scene_device_device_status";
    private static final String KEY_SCENE_DEVICE_IS_DELETED = "_scene_device_is_deleted";
    private static final String CREATE_TABLE_SCENE_DEVICE = "CREATE TABLE "
            + TABLE_SCENE_DEVICE + "(" + KEY_SCENE_DEVICE_SCENE_ID
            + " INTEGER," + KEY_SCENE_DEVICE_DEVICE_ID + " INTEGER,"
            + KEY_SCENE_DEVICE_DEVICE_STATUS + " INTEGER,"
            + KEY_SCENE_DEVICE_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE ST_WHEN
    private static final String TABLE_ST_WHEN = "st_when";
    private static final String KEY_ST_WHEN_ID = "_st_when_id";
    private static final String KEY_ST_WHEN_DEVICE_ID = "_st_when_device_id";
    private static final String KEY_ST_WHEN_VALUE = "_st_when_value";
    private static final String KEY_ST_WHEN_TYPE = "_st_when_type";
    private static final String KEY_ST_WHEN_IS_DELETED = "_st_when_is_deleted";
    private static final String CREATE_TABLE_ST_WHEN = "CREATE TABLE "
            + TABLE_ST_WHEN + "(" + KEY_ST_WHEN_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ST_WHEN_DEVICE_ID
            + " INTEGER," + KEY_ST_WHEN_VALUE + " TEXT," + KEY_ST_WHEN_TYPE
            + " INTEGER," + KEY_ST_WHEN_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE ST_TRIGGER
    private static final String TABLE_ST_TRIGGER = "st_trigger";
    private static final String KEY_ST_TRIGGER_ID = "_st_trigger_id";
    private static final String KEY_ST_TRIGGER_DEVICE_ID = "_st_trigger_device_id";
    private static final String KEY_ST_TRIGGER_VALUE = "_st_trigger_value";
    private static final String KEY_ST_TRIGGER_ACTION = "_st_trigger_action";
    private static final String KEY_ST_TRIGGER_IS_DELETED = "_st_trigger_is_deleted";
    private static final String CREATE_TABLE_ST_TRIGGER = "CREATE TABLE "
            + TABLE_ST_TRIGGER + "(" + KEY_ST_TRIGGER_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ST_TRIGGER_DEVICE_ID
            + " INTEGER," + KEY_ST_TRIGGER_VALUE + " TEXT,"
            + KEY_ST_TRIGGER_ACTION + " INTEGER," + KEY_ST_TRIGGER_IS_DELETED
            + " INTEGER DEFAULT 0" + ")";
    
    // TABLE ST_WHEN_TRIGGER
    private static final String TABLE_ST_WHEN_TRIGGER = "st_when_trigger";
    private static final String KEY_ST_WHEN_TRIGGER_WHEN_ID = "_st_when_trigger_when_id";
    private static final String KEY_ST_WHEN_TRIGGER_TRIGGER_ID = "_st_when_trigger_trigger_id";
    private static final String KEY_ST_WHEN_TRIGGER_NAME = "_st_when_trigger_name";
    private static final String KEY_ST_WHEN_TRIGGER_IS_DELETED = "_st_when_trigger_is_deleted";
    private static final String KEY_ST_WHEN_TRIGGER_ONOFF = "st_when_trigger_onoff";
    private static final String CREATE_TABLE_ST_WHEN_TRIGGER = "CREATE TABLE "
            + TABLE_ST_WHEN_TRIGGER + "(" + KEY_ST_WHEN_TRIGGER_WHEN_ID
            + " INTEGER," + KEY_ST_WHEN_TRIGGER_TRIGGER_ID + " INTEGER,"
            + KEY_ST_WHEN_TRIGGER_NAME + " TEXT,"
            + KEY_ST_WHEN_TRIGGER_IS_DELETED + " INTEGER DEFAULT 0,"
            + KEY_ST_WHEN_TRIGGER_ONOFF + " INTEGER DEFAULT 0" + ")";
    
    // TABLE WIFI
    private static final String TABLE_WIFI = "wifi";
    private static final String KEY_WIFI_ID = "_wifi_id";
    private static final String KEY_WIFI_NAME = "_wifi_name";
    private static final String KEY_WIFI_PASSWORD = "_wifi_password";
    private static final String KEY_WIFI_CODE = "_wifi_code";
    private static final String KEY_WIFI_IS_DELETED = "_wifi_is_deleted";
    private static final String CREATE_TABLE_WIFI = "CREATE TABLE "
            + TABLE_WIFI + "(" + KEY_WIFI_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_WIFI_NAME + " TEXT,"
            + KEY_WIFI_PASSWORD + " TEXT," + KEY_WIFI_CODE + " TEXT,"
            + KEY_WIFI_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE BOX
    private static final String TABLE_BOX = "box";
    private static final String KEY_BOX_ID = "_box_id";
    private static final String KEY_BOX_NAME = "_box_name";
    private static final String KEY_BOX_PASSWORD = "_box_password";
    private static final String KEY_BOX_CODE = "_box_code";
    private static final String KEY_BOX_WIFI_ID = "_box_wifi_id";
    private static final String KEY_BOX_IS_DELETED = "_box_is_deleted";
    private static final String CREATE_TABLE_BOX = "CREATE TABLE " + TABLE_BOX
            + "(" + KEY_BOX_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_BOX_NAME + " TEXT," + KEY_BOX_PASSWORD + " TEXT,"
            + KEY_BOX_CODE + " TEXT," + KEY_BOX_WIFI_ID + " INTEGER,"
            + KEY_BOX_IS_DELETED + " INTEGER DEFAULT 0" + ")";
    
    // TABLE USER
    private static final String TABLE_USER = "user";
    private static final String KEY_USER_ID = "_user_id";
    private static final String KEY_USER_NAME = "_user_name";
    private static final String KEY_USER_EMAIL = "_user_email";
    private static final String KEY_USER_ICON = "_user_icon";
    private static final String KEY_USER_STATUS = "_user_status";
    private static final String KEY_USER_IS_DELETED = "_user_is_deleted";
    private static final String CREATE_TABLE_USER = "CREATE TABLE "
            + TABLE_USER + "(" + KEY_USER_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_USER_NAME + " TEXT,"
            + KEY_USER_EMAIL + " TEXT," + KEY_USER_ICON + " TEXT,"
            + KEY_USER_STATUS + " INTEGER," + KEY_USER_IS_DELETED
            + " INTEGER DEFAULT 0" + ")";
    
    // TABLE HISTORY_BOX_WIFI
    private static final String TABLE_HISTORY_BOX_WIFI = "history_box_wifi";
    private static final String KEY_HISTORY_BOX_WIFI_ID = "_history_box_wifi_id";
    private static final String KEY_HISTORY_BOX_WIFI_BOX_ID = "_history_box_wifi_box_id";
    private static final String KEY_HISTORY_BOX_WIFI_WIFI_ID = "_history_box_wifi_wifi_id";
    private static final String KEY_HISTORY_BOX_WIFI_USER_ID = "_history_box_wifi_user_id";
    private static final String KEY_HISTORY_BOX_WIFI_TYPE = "_history_box_wifi_type";
    private static final String KEY_HISTORY_BOX_WIFI_STATUS = "_history_box_wifi_status";
    private static final String KEY_HISTORY_BOX_WIFI_DATETIME = "_history_box_wifi_datetime";
    private static final String CREATE_TABLE_HISTORY_BOX_WIFI = "CREATE TABLE "
            + TABLE_HISTORY_BOX_WIFI + "(" + KEY_HISTORY_BOX_WIFI_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_BOX_WIFI_BOX_ID + " INTEGER,"
            + KEY_HISTORY_BOX_WIFI_WIFI_ID + " INTEGER,"
            + KEY_HISTORY_BOX_WIFI_USER_ID + " INTEGER,"
            + KEY_HISTORY_BOX_WIFI_TYPE + " INTEGER,"
            + KEY_HISTORY_BOX_WIFI_STATUS + " TEXT,"
            + KEY_HISTORY_BOX_WIFI_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_USER_ACTION
    private static final String TABLE_HISTORY_USER_ACTION = "history_user_action";
    private static final String KEY_HISTORY_USER_ACTION_ID = "_history_user_action_id";
    private static final String KEY_HISTORY_USER_ACTION_USER_ID = "_history_user_action_user_id";
    private static final String KEY_HISTORY_USER_ACTION_TYPE = "_history_user_action_type";
    private static final String KEY_HISTORY_USER_ACTION_BY_USER_ID = "_history_user_action_by_user_id";
    private static final String KEY_HISTORY_USER_ACTION_BOX_ID = "_history_user_action_box_id";
    private static final String KEY_HISTORY_USER_ACTION_DATETIME = "_history_user_action_datetime";
    private static final String CREATE_TABLE_HISTORY_USER_ACTION = "CREATE TABLE "
            + TABLE_HISTORY_USER_ACTION
            + "("
            + KEY_HISTORY_USER_ACTION_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_USER_ACTION_USER_ID
            + " INTEGER,"
            + KEY_HISTORY_USER_ACTION_TYPE
            + " INTEGER,"
            + KEY_HISTORY_USER_ACTION_BY_USER_ID
            + " INTEGER,"
            + KEY_HISTORY_USER_ACTION_BOX_ID
            + " INTEGER,"
            + KEY_HISTORY_USER_ACTION_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_USER_HOME
    private static final String TABLE_HISTORY_USER_HOME = "history_user_home";
    private static final String KEY_HISTORY_USER_HOME_ID = "_history_user_home_id";
    private static final String KEY_HISTORY_USER_HOME_USER_ID = "_history_user_home_user_id";
    private static final String KEY_HISTORY_USER_HOME_TYPE = "_history_user_home_type";
    private static final String KEY_HISTORY_USER_HOME_DATETIME = "_history_user_home_datetime";
    private static final String CREATE_TABLE_HISTORY_USER_HOME = "CREATE TABLE "
            + TABLE_HISTORY_USER_HOME
            + "("
            + KEY_HISTORY_USER_HOME_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_USER_HOME_USER_ID
            + " INTEGER,"
            + KEY_HISTORY_USER_HOME_TYPE
            + " INTEGER,"
            + KEY_HISTORY_USER_HOME_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_DEVICE
    private static final String TABLE_HISTORY_DEVICE = "history_device";
    private static final String KEY_HISTORY_DEVICE_ID = "_history_device_id";
    private static final String KEY_HISTORY_DEVICE_TYPE = "_history_device_type";
    private static final String KEY_HISTORY_DEVICE_USER_ID = "_history_device_user_id";
    private static final String KEY_HISTORY_DEVICE_DEVICE_ID = "_history_device_device_id";
    private static final String KEY_HISTORY_DEVICE_SCENE_ID = "_history_device_scene_id";
    private static final String KEY_HISTORY_DEVICE_DATETIME = "_history_device_datetime";
    
    private static final String CREATE_TABLE_HISTORY_DEVICE = "CREATE TABLE "
            + TABLE_HISTORY_DEVICE + "(" + KEY_HISTORY_DEVICE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_DEVICE_USER_ID + " INTEGER,"
            + KEY_HISTORY_DEVICE_DEVICE_ID + " INTEGER,"
            + KEY_HISTORY_DEVICE_SCENE_ID + " INTEGER,"
            + KEY_HISTORY_DEVICE_TYPE + " INTEGER,"
            
            + KEY_HISTORY_DEVICE_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_PLACE
    private static final String TABLE_HISTORY_PLACE = "history_place";
    private static final String KEY_HISTORY_PLACE_ID = "_history_place_id";
    private static final String KEY_HISTORY_PLACE_PLACE_ID = "_history_place_place_id";
    private static final String KEY_HISTORY_PLACE_TYPE = "_history_place_type";
    private static final String KEY_HISTORY_PLACE_USER_ID = "_history_place_user_id";
    private static final String KEY_HISTORY_PLACE_DATETIME = "_history_place_datetime";
    private static final String CREATE_TABLE_HISTORY_PLACE = "CREATE TABLE "
            + TABLE_HISTORY_PLACE + "(" + KEY_HISTORY_PLACE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_PLACE_PLACE_ID + " INTEGER," + KEY_HISTORY_PLACE_TYPE
            + " INTEGER," + KEY_HISTORY_PLACE_USER_ID + " INTEGER,"
            + KEY_HISTORY_PLACE_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_SCENE
    private static final String TABLE_HISTORY_SCENE = "history_scene";
    private static final String KEY_HISTORY_SCENE_ID = "_history_scene_id";
    private static final String KEY_HISTORY_SCENE_SCENE_ID = "_history_scene_scene_id";
    private static final String KEY_HISTORY_SCENE_TYPE = "_history_scene_type";
    private static final String KEY_HISTORY_SCENE_USER_ID = "_history_scene_user_id";
    private static final String KEY_HISTORY_SCENE_DATETIME = "_history_scene_datetime";
    private static final String CREATE_TABLE_HISTORY_SCENE = "CREATE TABLE "
            + TABLE_HISTORY_SCENE + "(" + KEY_HISTORY_SCENE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_SCENE_SCENE_ID + " INTEGER," + KEY_HISTORY_SCENE_TYPE
            + " INTEGER," + KEY_HISTORY_SCENE_USER_ID + " INTEGER,"
            + KEY_HISTORY_SCENE_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_SMART_TRIGGER
    private static final String TABLE_HISTORY_SMART_TRIGGER = "history_smart_trigger";
    private static final String KEY_HISTORY_SMART_TRIGGER_ID = "_history_smart_trigger_id";
    private static final String KEY_HISTORY_SMART_TRIGGER_WHEN_TRIGGER_ID = "_history_smart_trigger_when_trigger_id";
    private static final String KEY_HISTORY_SMART_TRIGGER_TYPE = "_history_smart_trigger_type";
    private static final String KEY_HISTORY_SMART_TRIGGER_USER_ID = "_history_smart_trigger_user_id";
    private static final String KEY_HISTORY_SMART_TRIGGER_DATETIME = "_history_smart_trigger_datetime";
    private static final String CREATE_TABLE_HISTORY_SMART_TRIGGER = "CREATE TABLE "
            + TABLE_HISTORY_SMART_TRIGGER
            + "("
            + KEY_HISTORY_SMART_TRIGGER_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_SMART_TRIGGER_WHEN_TRIGGER_ID
            + " INTEGER,"
            + KEY_HISTORY_SMART_TRIGGER_TYPE
            + " INTEGER,"
            + KEY_HISTORY_SMART_TRIGGER_USER_ID
            + " INTEGER,"
            + KEY_HISTORY_SMART_TRIGGER_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_WARNING
    private static final String TABLE_HISTORY_WARNING = "history_warning";
    private static final String KEY_HISTORY_WARNING_ID = "_history_warning_id";
    private static final String KEY_HISTORY_WARNING_DEVICE_ID = "_history_warning_device_id";
    private static final String KEY_HISTORY_WARNING_DATETIME = "_history_warning_datetime";
    private static final String CREATE_TABLE_HISTORY_WARNING = "CREATE TABLE "
            + TABLE_HISTORY_WARNING + "(" + KEY_HISTORY_WARNING_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_WARNING_DEVICE_ID + " INTEGER,"
            + KEY_HISTORY_WARNING_DATETIME + " TEXT" + ")";
    
    // TABLE HISTORY_MOVE_PLACE_OF_DEVICE
    private static final String TABLE_HISTORY_MOVE_PLACE_OF_DEVICE = "history_move_place_of_device";
    private static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID = "_history_move_place_of_device_id";
    private static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID = "_history_move_place_of_device_device_id";
    private static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID = "_history_move_place_of_device_place_id";
    private static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID = "_history_move_place_of_device_user_id";
    private static final String KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME = "_history_move_place_of_device_datetime";
    private static final String CREATE_TABLE_HISTORY_MOVE_PLACE_OF_DEVICE = "CREATE TABLE "
            + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE
            + "("
            + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID
            + " INTEGER,"
            + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID
            + " INTEGER,"
            + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID
            + " INTEGER,"
            + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME + " TEXT" + ")";
    
    // TABLE s
    private static final String TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE = "history_name_capability_of_place";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID = "_history_name_capability_of_place_id";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID = "_history_name_capability_of_place_place_id";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID = "_history_name_capability_of_place_user_id";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM = "_history_name_capability_of_place_rename_from";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO = "_history_name_capability_of_place_rename_to";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM = "_history_name_capability_of_place_capability_from";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO = "_history_name_capability_of_place_capability_to";
    private static final String KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME = "_history_name_capability_of_place_datetime";
    private static final String CREATE_TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE = "CREATE TABLE "
            + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE
            + "("
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID
            + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID
            + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM
            + " TEXT,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO
            + " TEXT,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM
            + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO
            + " INTEGER,"
            + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME
            + " TEXT" + ")";
    
    public SQLiteAsusNewHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        
    }
    
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(CREATE_TABLE_DEVICE);
        database.execSQL(CREATE_TABLE_PLACE);
        database.execSQL(CREATE_TABLE_SCENE);
        database.execSQL(CREATE_TABLE_SCENE_DEVICE);
        database.execSQL(CREATE_TABLE_ST_WHEN);
        database.execSQL(CREATE_TABLE_ST_TRIGGER);
        database.execSQL(CREATE_TABLE_ST_WHEN_TRIGGER);
        database.execSQL(CREATE_TABLE_WIFI);
        database.execSQL(CREATE_TABLE_BOX);
        database.execSQL(CREATE_TABLE_USER);
        database.execSQL(CREATE_TABLE_HISTORY_BOX_WIFI);
        database.execSQL(CREATE_TABLE_HISTORY_USER_ACTION);
        database.execSQL(CREATE_TABLE_HISTORY_USER_HOME);
        database.execSQL(CREATE_TABLE_HISTORY_DEVICE);
        database.execSQL(CREATE_TABLE_HISTORY_PLACE);
        database.execSQL(CREATE_TABLE_HISTORY_SCENE);
        database.execSQL(CREATE_TABLE_HISTORY_SMART_TRIGGER);
        database.execSQL(CREATE_TABLE_HISTORY_WARNING);
        database.execSQL(CREATE_TABLE_HISTORY_MOVE_PLACE_OF_DEVICE);
        database.execSQL(CREATE_TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE);
        
        // add default record for PLACE UNCATEGORIZED
        database.execSQL("INSERT INTO " + TABLE_PLACE + "(" + KEY_PLACE_ID
                + "," + KEY_PLACE_NAME + "," + KEY_PLACE_ICON + ","
                + KEY_PLACE_CAPABILITY_TYPE + "," + KEY_PLACE_IS_DELETED
                + ") values(1, \"Uncategorized\", \"\", 0, 0)");
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_DEVICE);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_PLACE);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_SCENE);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_SCENE_DEVICE);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_ST_WHEN);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_ST_TRIGGER);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_ST_WHEN_TRIGGER);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_WIFI);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_BOX);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_USER);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_HISTORY_BOX_WIFI);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_HISTORY_USER_ACTION);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_HISTORY_USER_HOME);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_HISTORY_DEVICE);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_HISTORY_PLACE);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_HISTORY_SCENE);
        db.execSQL("DROP TABLE if (EXISTS "
                + CREATE_TABLE_HISTORY_SMART_TRIGGER);
        db.execSQL("DROP TABLE if (EXISTS " + CREATE_TABLE_HISTORY_WARNING);
        db.execSQL("DROP TABLE if (EXISTS "
                + CREATE_TABLE_HISTORY_MOVE_PLACE_OF_DEVICE);
        db.execSQL("DROP TABLE if (EXISTS "
                + CREATE_TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE);
        onCreate(db);
    }
    
    /*
     * ADD NEW DEVICE CARD
     */
    public int addDevice(DeviceItem item) {
        SQLiteDatabase db = null;
        
        try {
            Log.e("Database", this.getDatabaseName());
            db = this.getWritableDatabase();
        } catch (Exception e) {
            Log.e("Database LOI LOI", "hieu gi khong???" + e.getMessage());
            return -3;
        }
        
        if (db == null) {
            return -2;
        }
        
        ContentValues values = new ContentValues();
        
        values.put(KEY_DEVICE_NAME, item.getDeviceName());
        values.put(KEY_DEVICE_ICON, item.getDeviceIcon());
        values.put(KEY_DEVICE_STATUS, item.getDeviceStatus());
        values.put(KEY_DEVICE_TYPE, item.getDeviceType());
        values.put(KEY_DEVICE_CONTROL_VALUE, item.getStatusValue());
        values.put(KEY_DEVICE_CODE, item.getDeviceCode());
        values.put(KEY_DEVICE_BOX_ID, item.getDeviceBoxId());
        values.put(KEY_DEVICE_PLACE_ID, item.getDevicePlaceId());
        values.put(KEY_DEVICE_USER_ID, item.getUserId());
        
        int id = (int) db.insert(TABLE_DEVICE, null, values);
        if (db != null) {
            db.close();
        }
        
        // add history
        updateHistoryDevice(id, 1, 0);
        
        return id;
    }
    
    /*
     * public void updateDevice(DeviceItem deviceItem) { if (deviceItem == null)
     * { return; } SQLiteDatabase db = this.getReadableDatabase(); }
     */
    public boolean updateDevice(DeviceItem deviceItem) {
        boolean vRet = false;
        if (deviceItem != null) {
            SQLiteDatabase db = this.getWritableDatabase();
            try {
                ContentValues values = new ContentValues();
                
                values.put(KEY_DEVICE_NAME, deviceItem.getDeviceName());
                values.put(KEY_DEVICE_ICON, deviceItem.getDeviceIcon());
                values.put(KEY_DEVICE_STATUS, deviceItem.getDeviceStatus());
                values.put(KEY_DEVICE_TYPE, deviceItem.getDeviceType());
                values.put(KEY_DEVICE_CONTROL_VALUE,
                        deviceItem.getStatusValue());
                values.put(KEY_DEVICE_CONTROL_VALUE,
                        deviceItem.getStatusValue());
                values.put(KEY_DEVICE_CODE, deviceItem.getDeviceCode());
                values.put(KEY_DEVICE_BOX_ID, deviceItem.getDeviceBoxId());
                values.put(KEY_DEVICE_PLACE_ID, deviceItem.getDevicePlaceId());
                values.put(KEY_DEVICE_USER_ID, deviceItem.getUserId());
                values.put(KEY_DEVICE_IS_DELETED, deviceItem.getIsDeleted());
                
                db.update(
                        TABLE_DEVICE,
                        values,
                        KEY_DEVICE_ID + " = ? ",
                        new String[] {String.valueOf(deviceItem.getDeviceId())});
                
                vRet = true;
                
                if (db != null) {
                    db.close();
                }
                
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        return vRet;
    }
    
    public int renamePlace(PlaceItem placeItem) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_PLACE_NAME, placeItem.getPlaceName());
        
        int id = db.update(TABLE_PLACE, values,
                KEY_PLACE_ID + "=" + String.valueOf(placeItem.getPlaceId()),
                null);
        if (db != null) {
            db.close();
        }
        return id;
    }
    
    // RENAME DEVICE NAMNT16
    public int renameDevice(DeviceItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_NAME, item.getDeviceName());
        
        int id = db.update(TABLE_DEVICE, values,
                KEY_DEVICE_ID + "=" + String.valueOf(item.getDeviceId()), null);
        if (db != null) {
            
            db.close();
        }
        return id;
    }
    
    // REMOVE DEVICE NAMNT16
    public int removeDevice(DeviceItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_IS_DELETED, 1);
        int id = db.update(TABLE_DEVICE, values,
                KEY_DEVICE_ID + "=" + String.valueOf(item.getDeviceId()), null);
        if (db != null) {
            
            db.close();
        }
        // update history
        
        updateHistoryDevice(item.getDeviceId(), 1, 1);
        
        return id;
    }
    
    // Edit place DEVICE NAMNT16
    public int editPlaceDevice(DeviceItem item) {
        int id = -1;
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            ContentValues values = new ContentValues();
            values.put(KEY_DEVICE_PLACE_ID, item.getDevicePlaceId());
            
            id = db.update(TABLE_DEVICE, values,
                    KEY_DEVICE_ID + "=" + String.valueOf(item.getDeviceId()),
                    null);
            if (db != null) {
                db.close();
            }
        } catch (SQLException e) {
            Log.e("editPlaceDevice", "editPlaceDevice" + e.toString());
        }
        
        return id;
    }
    
    // Edit place capability NAMNT16
    public int changeCapabilityPlace(PlaceItem item) {
        int id = -1;
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            ContentValues values = new ContentValues();
            values.put(KEY_PLACE_CAPABILITY_TYPE, item.getCapabilityType());
            
            id = db.update(TABLE_PLACE, values,
                    KEY_PLACE_ID + "=" + String.valueOf(item.getPlaceId()),
                    null);
            if (db != null) {
                db.close();
            }
        } catch (SQLException e) {
            Log.e("editPlaceDevice", "editPlaceDevice" + e.toString());
        }
        
        return id;
    }
    
    // get place id NamNT16
    public int getPlaceId(PlaceItem item) {
        String selectQuery = "SELECT " + KEY_PLACE_ID + " FROM " + TABLE_PLACE
                + " WHERE " + KEY_PLACE_NAME + "='" + item.getPlaceName()
                + "' AND " + KEY_PLACE_IS_DELETED + " = 0";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
            }
        }
        
        return 0;
        
    }
    
    // get list device - use in smart_trigger Namnt16 ver1
    // get place id NamNT16
    public PlaceItem getPlaceItembyId(int placeId) {
        PlaceItem vRet = null;
        try {
            
            String selectQuery = "SELECT *  FROM " + TABLE_PLACE + " WHERE "
                    + KEY_PLACE_ID + "='" + placeId + "' AND "
                    + KEY_PLACE_IS_DELETED + " = 0";
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
            if (cursorSqliteAsusHelper.getCount() > 0) {
                if (cursorSqliteAsusHelper.moveToFirst()) {
                    
                    vRet = new PlaceItem();
                    vRet.setPlaceId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    vRet.setPlaceName(cursorSqliteAsusHelper.getString(1));
                    vRet.setPlaceImage(cursorSqliteAsusHelper.getString(2));
                    vRet.setCapabilityType(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(3)));
                    
                }
            }
            
        } catch (SQLException e) {
            Log.e("getPlaceItembyId", "getPlaceItembyId : " + e.toString());
        }
        
        return vRet;
        
    }
    
    public ArrayList<DeviceItem> getListDeviceTrigg() {
        ArrayList<DeviceItem> list = new ArrayList<DeviceItem>();
        String selectQuery = "SELECT * FROM " + TABLE_DEVICE;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    DeviceItem device = new DeviceItem();
                    device.setDeviceId(Integer.parseInt(cursor.getString(0)));
                    device.setDeviceName(cursor.getString(1));
                    device.setDeviceIcon(cursor.getString(2));
                    device.setDeviceType(Integer.parseInt(cursor.getString(4)));
                    // device.setStatusValue(Integer.parseInt(cursor.getString(4)));
                    // device.setDeviceStatus(Integer.parseInt(cursor.getString(5)));
                    
                    list.add(device);
                } while (cursor.moveToNext());
            }
            return list;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    // insert table smart_trigger Namnt16 ver1 test
    // please do not delete rela method
    public int sequentialInsertSmartTrigger(SmartTriggerItem item) {
        int id = -1;
        int whenid, triggerid;
        SmartTriggerItem smartitem = item;
        whenid = insertWhentable(smartitem);
        triggerid = insertTriggertable(smartitem);
        if (whenid > 0 && triggerid > 0) {
            smartitem.setWhenId(whenid);
            smartitem.setTriggerId(triggerid);
            id = insertTriggerWhentable(smartitem);
        }
        return id;
    }
    
    public int insertWhentable(SmartTriggerItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(KEY_ST_WHEN_DEVICE_ID, item.getWhenDeviceId());
        values.put(KEY_ST_WHEN_VALUE, item.getWhenDeviceValues());
        values.put(KEY_ST_WHEN_TYPE, item.getWhenDeviceType());
        // values.put(KEY_ST_WHEN_TRIGGER_IS_DELETED, a5);
        
        int id = (int) db.insert(TABLE_ST_WHEN, null, values);
        if (db != null) {
            db.close();
        }
        return id;
    }
    
    public int insertTriggertable(SmartTriggerItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(KEY_ST_TRIGGER_DEVICE_ID, item.getTriggerDeviceId());
        values.put(KEY_ST_TRIGGER_VALUE, item.getTriggerDeveceValues());
        values.put(KEY_ST_TRIGGER_ACTION, item.getTriggerDeviceType());
        // values.put(KEY_ST_WHEN_TRIGGER_IS_DELETED, a5);
        
        int id = (int) db.insert(TABLE_ST_TRIGGER, null, values);
        if (db != null) {
            db.close();
        }
        return id;
    }
    
    public int insertTriggerWhentable(SmartTriggerItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(KEY_ST_WHEN_TRIGGER_WHEN_ID, item.getWhenId());
        values.put(KEY_ST_WHEN_TRIGGER_TRIGGER_ID, item.getTriggerId());
        values.put(KEY_ST_WHEN_TRIGGER_NAME, item.getSmarttriggername());
        // values.put(KEY_ST_WHEN_TRIGGER_IS_DELETED, a5);
        
        int id = (int) db.insert(TABLE_ST_WHEN_TRIGGER, null, values);
        if (db != null) {
            db.close();
        }
        return id;
    }
    
    // get device id form table when NamNT16
    public int getdeviceIdformWhen(SmartTriggerItem item) {
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_WHEN_DEVICE_ID + " FROM "
                + TABLE_ST_WHEN + " WHERE " + KEY_ST_WHEN_ID + "="
                + item.getWhenId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
            }
        }
        
        return i;
        
    }
    
    // get device id form table trigger NamNT16
    public int getdeviceIdformTrigger(SmartTriggerItem item) {
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_TRIGGER_DEVICE_ID + " FROM "
                + TABLE_ST_TRIGGER + " WHERE " + KEY_ST_TRIGGER_ID + "="
                + item.getTriggerId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
            }
        }
        if (db != null) {
            db.close();
        }
        return i;
        
    }
    
    // get device id form table when NamNT16
    public int getwhenValformWhen(SmartTriggerItem item) {
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_WHEN_VALUE + " FROM "
                + TABLE_ST_WHEN + " WHERE " + KEY_ST_WHEN_ID + "="
                + item.getWhenId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
            }
        }
        if (db != null) {
            db.close();
        }
        return i;
        
    }
    
    // get device id form table when NamNT16
    public int gettriggerValformTrigger(SmartTriggerItem item) {
        int i = -1;
        String selectQuery = "SELECT " + KEY_ST_TRIGGER_VALUE + " FROM "
                + TABLE_ST_TRIGGER + " WHERE " + KEY_ST_TRIGGER_ID + "="
                + item.getTriggerId() + "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                return Integer.parseInt(cursorSqliteAsusHelper.getString(0));
            }
        }
        if (db != null) {
            db.close();
        }
        return i;
        
    }
    
    // smart trigger list namnt16
    public ArrayList<SmartTriggerItem> getsmartTriggerList() {
        ArrayList<SmartTriggerItem> arrSmartTriggerItems = new ArrayList<SmartTriggerItem>();
        
        // SmartTriggerItem smartTriggerItem=new SmartTriggerItem();
        String selectQuery = "SELECT * FROM " + TABLE_ST_WHEN_TRIGGER
                + " WHERE " + KEY_ST_WHEN_TRIGGER_IS_DELETED + " != 1";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            
            if (cursor.moveToFirst()) {
                do {
                    SmartTriggerItem smartTriggerItem = new SmartTriggerItem();
                    // smartTriggerItem.setSmartTriggerID(cursor.getInt(0));
                    smartTriggerItem.setWhenId(cursor.getInt(0));
                    smartTriggerItem.setTriggerId(cursor.getInt(1));
                    smartTriggerItem.setSmarttriggername(cursor.getString(2));
                    smartTriggerItem.setIconControl(cursor.getInt(4));
                    arrSmartTriggerItems.add(smartTriggerItem);
                } while (cursor.moveToNext());
                
            }
            // db.close();
            
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return arrSmartTriggerItems;
    }
    
    // get device from smart trigger Namnt16
    public DeviceItem getDeviceByfromTrigger(int deviceId) {
        
        DeviceItem deviceItem = new DeviceItem();
        String selectQuery = "SELECT * FROM " + TABLE_DEVICE + " WHERE "
                + KEY_DEVICE_ID + " = '" + deviceId + "' AND "
                + KEY_DEVICE_IS_DELETED + " != 1";
        
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);
            if (cursor.moveToFirst()) {
                
                deviceItem.setDeviceId(Integer.parseInt(cursor.getString(0)));
                deviceItem.setDeviceName(cursor.getString(1));
                deviceItem.setDeviceIcon(cursor.getString(2));
                deviceItem
                        .setDeviceStatus(Integer.parseInt(cursor.getString(3)));
                deviceItem.setDeviceType(Integer.parseInt(cursor.getString(4)));
                deviceItem
                        .setStatusValue(Integer.parseInt(cursor.getString(5)));
                deviceItem.setDeviceCode(cursor.getString(6));
                deviceItem
                        .setDeviceBoxId(Integer.parseInt(cursor.getString(7)));
                deviceItem.setDevicePlaceId(Integer.parseInt(cursor
                        .getString(8)));
                deviceItem.setUserId(Integer.parseInt(cursor.getString(9)));
                deviceItem.setIsDeleted(Integer.parseInt(cursor.getString(10)));
                
            }
            db.close();
            
        } catch (Exception e) {
            
        }
        return deviceItem;
    }
    
    // ONOFF smart trigger NAMNT16
    public int onoffSmartTrigger(SmartTriggerItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_ST_WHEN_TRIGGER_ONOFF, item.getIconControl());
        
        int id = db.update(TABLE_ST_WHEN_TRIGGER, values, KEY_ST_WHEN_ID + "="
                + String.valueOf(item.getWhenId()), null);
        if (db != null) {
            
            db.close();
        }
        return id;
    }
    
    //
    public DeviceItem getDeviceById(int deviceId) {
        
        DeviceItem deviceItem = new DeviceItem();
        String selectQuery = "SELECT * FROM " + TABLE_DEVICE + " WHERE "
                + KEY_DEVICE_ID + " = '" + deviceId + "' AND "
                + KEY_DEVICE_IS_DELETED + " != 1";
        
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);
            if (cursor.moveToFirst()) {
                
                deviceItem.setDeviceId(Integer.parseInt(cursor.getString(0)));
                deviceItem.setDeviceName(cursor.getString(1));
                deviceItem.setDeviceIcon(cursor.getString(2));
                deviceItem
                        .setDeviceStatus(Integer.parseInt(cursor.getString(3)));
                deviceItem.setDeviceType(Integer.parseInt(cursor.getString(4)));
                deviceItem
                        .setStatusValue(Integer.parseInt(cursor.getString(5)));
                deviceItem.setDeviceCode(cursor.getString(6));
                deviceItem
                        .setDeviceBoxId(Integer.parseInt(cursor.getString(7)));
                deviceItem.setDevicePlaceId(Integer.parseInt(cursor
                        .getString(8)));
                deviceItem.setUserId(Integer.parseInt(cursor.getString(9)));
                deviceItem.setIsDeleted(Integer.parseInt(cursor.getString(10)));
                
            }
            db.close();
            
        } catch (Exception e) {
            
        }
        return deviceItem;
    }
    
    /*
     * Check exist place name
     */
    public boolean checkExistPlace(PlaceItem item) {
        String selectQuery = "SELECT " + KEY_PLACE_ID + " FROM " + TABLE_PLACE
                + " WHERE " + KEY_PLACE_NAME + "='" + item.getPlaceName() + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /*
     * Add new Place
     */
    public int addPlace(PlaceItem item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_PLACE_NAME, item.getPlaceName());
            values.put(KEY_PLACE_ICON, item.getPlaceImage());
            values.put(KEY_PLACE_CAPABILITY_TYPE, item.getCapabilityType());
            values.put(KEY_PLACE_IS_DELETED, 0);
            try {
                id = (int) db.insert(TABLE_PLACE, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
            
        }
        // add data to history
        updateHistoryPlace(id, 1, 0);
        return id;
        
    }
    
    public int addHistoryPlace(HistoryPlaceItem item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_PLACE_PLACE_ID,
                    item.getHistoryPlacePlaceId());
            values.put(KEY_HISTORY_PLACE_TYPE, item.getHistoryPlaceType());
            values.put(KEY_HISTORY_PLACE_USER_ID, item.getHistoryPlaceUserId());
            values.put(KEY_HISTORY_PLACE_DATETIME, item.getHistoryPlaceDate());
            try {
                id = (int) db.insert(TABLE_HISTORY_PLACE, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    /*
     * Get list all place
     */
    public ArrayList<PlaceItem> getListPlace() {
        ArrayList<PlaceItem> lstPlace = new ArrayList<PlaceItem>();
        String selectQuery = "SELECT " + TABLE_PLACE + "." + KEY_PLACE_ID
                + ", " + TABLE_PLACE + "." + KEY_PLACE_NAME + ", "
                + TABLE_PLACE + "." + KEY_PLACE_ICON + ", " + TABLE_PLACE + "."
                + KEY_PLACE_CAPABILITY_TYPE + "," + "COUNT (" + TABLE_DEVICE
                + "." + KEY_DEVICE_ID + ") as DEVICE_NUM" +
                
                " FROM " + TABLE_PLACE
                
                + " LEFT OUTER JOIN " + TABLE_DEVICE + " ON " + TABLE_DEVICE
                + "." + KEY_DEVICE_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID;
        
        // where device and place is not deleted
        selectQuery += " WHERE " + TABLE_PLACE + "." + KEY_PLACE_IS_DELETED
                + " = 0 AND " + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED
                + " = 0 ";
        
        selectQuery += " GROUP BY " + TABLE_PLACE + "." + KEY_PLACE_ID;
        selectQuery += " ORDER BY " + TABLE_PLACE + "." + KEY_PLACE_ID
                + " DESC";
        
        Log.e("getListPlace", "getListPlace: " + selectQuery);
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    PlaceItem place = new PlaceItem();
                    place.setPlaceId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    place.setPlaceName(cursorSqliteAsusHelper.getString(1));
                    place.setPlaceImage(cursorSqliteAsusHelper.getString(2));
                    place.setCapabilityType(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(3)));
                    place.setDeviceNumber(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(4)));
                    lstPlace.add(place);
                } while (cursorSqliteAsusHelper.moveToNext());
            }
        } catch (SQLException e) {
            Log.e("getListPlace -- ", "getListPlace -- " + e.toString());
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return lstPlace;
    }
    
    public PlaceItem getInfoOfPlace(int placeID) {
        PlaceItem place = new PlaceItem();
        String selectQuery = "SELECT " + TABLE_PLACE + "." + KEY_PLACE_ID
                + ", " + TABLE_PLACE + "." + KEY_PLACE_NAME + ", "
                + TABLE_PLACE + "." + KEY_PLACE_ICON + ", " + TABLE_PLACE + "."
                + KEY_PLACE_CAPABILITY_TYPE + "," + "COUNT (" + TABLE_DEVICE
                + "." + KEY_DEVICE_ID + ") as DEVICE_NUM" + ","
                + TABLE_HISTORY_PLACE + "." + KEY_HISTORY_PLACE_DATETIME;
        selectQuery += " FROM " + TABLE_PLACE + " LEFT OUTER JOIN "
                + TABLE_DEVICE + " ON " + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID + " LEFT OUTER JOIN " + TABLE_HISTORY_PLACE
                + " ON " + TABLE_HISTORY_PLACE + "."
                + KEY_HISTORY_PLACE_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID;
        selectQuery += " WHERE " + TABLE_PLACE + "." + KEY_PLACE_ID + " = "
                + placeID;
        selectQuery += " ORDER BY " + TABLE_PLACE + "." + KEY_PLACE_ID
                + " DESC";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    place.setPlaceId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    place.setPlaceName(cursorSqliteAsusHelper.getString(1));
                    place.setPlaceImage(cursorSqliteAsusHelper.getString(2));
                    place.setCapabilityType(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(3)));
                    place.setDeviceNumber(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(4)));
                    place.setDateCreated(cursorSqliteAsusHelper.getString(5));
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return place;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
    /*
     * GET LIST DEVICE FROM PLACE_ID
     */
    public ArrayList<DeviceItem> getAllDevice(int placeId) {
        ArrayList<DeviceItem> listDevice = new ArrayList<DeviceItem>();
        
        String sql = "SELECT " + TABLE_DEVICE + "." + KEY_DEVICE_ID + ","
                + TABLE_DEVICE + "." + KEY_DEVICE_NAME + "," + TABLE_DEVICE
                + "." + KEY_DEVICE_ICON + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_STATUS + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_TYPE + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_CONTROL_VALUE + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_CODE + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_BOX_ID + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_USER_ID + "," + TABLE_PLACE + "." + KEY_PLACE_NAME
                + "," + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED
                
                + " FROM " + TABLE_DEVICE + " INNER JOIN " + TABLE_PLACE
                + " ON " + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID + " = "
                + TABLE_PLACE + "." + KEY_PLACE_ID;
        
        sql += " WHERE " + KEY_PLACE_ID + "=" + placeId;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        
        try {
            if (curso.moveToFirst() /* && curso2.moveToFirst() */) {
                do {
                    DeviceItem de = new DeviceItem();
                    
                    de.setDeviceId(Integer.parseInt(curso.getString(0)
                            .toString()));
                    de.setDeviceName(curso.getString(1));
                    de.setDeviceIcon(curso.getString(2));
                    de.setDeviceStatus(Integer.parseInt(curso.getString(3)
                            .toString()));
                    de.setDeviceType(Integer.parseInt(curso.getString(4)
                            .toString()));
                    de.setStatusValue(Integer.parseInt(curso.getString(5)
                            .toString()));
                    de.setDeviceCode(curso.getString(6));
                    de.setDeviceBoxId(Integer.parseInt(curso.getString(7)
                            .toString()));
                    de.setDevicePlaceId(Integer.parseInt(curso.getString(8)
                            .toString()));
                    de.setUserId(Integer
                            .parseInt(curso.getString(9).toString()));
                    de.setDevicePlace(curso.getString(10));
                    de.setIsDeleted(Integer.parseInt(curso.getString(11)));
                    
                    listDevice.add(de);
                    
                } while (curso.moveToNext());
                
            }
            
        } catch (Exception e) {
            
        }
        
        return listDevice;
    }
    
    public ArrayList<SceneItem> getSceneByPlaceId(int placeId) {
        ArrayList<SceneItem> listScene = new ArrayList<SceneItem>();
        
        String selectQuery = "SELECT " + TABLE_SCENE + "." + KEY_SCENE_ID
                + ", " + TABLE_SCENE + "." + KEY_SCENE_NAME + ", "
                + TABLE_SCENE + "." + KEY_SCENE_ICON + ", " + TABLE_SCENE + "."
                + KEY_SCENE_COLOR + ", " + TABLE_SCENE + "."
                + KEY_SCENE_PLACE_ID + ", " + TABLE_PLACE + "."
                + KEY_PLACE_NAME;
        
        selectQuery += " FROM " + TABLE_SCENE + " LEFT OUTER JOIN "
                + TABLE_PLACE + " ON " + TABLE_SCENE + "." + KEY_SCENE_PLACE_ID
                + " = " + TABLE_PLACE + "." + KEY_PLACE_ID;
        selectQuery += " WHERE " + TABLE_SCENE + "." + KEY_SCENE_PLACE_ID + "="
                + placeId;
        selectQuery += " AND " + TABLE_SCENE + "." + KEY_SCENE_IS_DELETED
                + " = " + 0;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(selectQuery, null);
        try {
            if (curso.moveToFirst()) {
                do {
                    
                    SceneItem itemScene = new SceneItem();
                    itemScene.setSceneId(Integer.parseInt(curso.getString(0)));
                    itemScene.setSceneTitle(curso.getString(1));
                    itemScene.setSceneIcon(curso.getString(2));
                    itemScene.setSceneBackground(Integer.parseInt(curso
                            .getString(3)));
                    
                    itemScene.setPlaceId(Integer.parseInt(curso.getString(4)));
                    
                    itemScene.setScenePlace(curso.getString(5));
                    listScene.add(itemScene);
                    
                } while (curso.moveToNext());
            }
            return listScene;
        } finally {
            if (curso != null) {
                curso.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
    /*
     * Get list Scene from Place
     */
    
    /*
     * Check exist scene name
     */
    public boolean checkExistScene(SceneItem itemScene) {
        String selectQuery = "SELECT " + KEY_SCENE_ID + " FROM " + TABLE_SCENE
                + " WHERE " + KEY_SCENE_NAME + "='" + itemScene.getSceneTitle()
                + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /*
     * Add new Scene
     */
    public int addScene(SceneItem itemScene) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_SCENE_NAME, itemScene.getSceneTitle());
            values.put(KEY_SCENE_ICON, itemScene.getSceneIcon());
            values.put(KEY_SCENE_COLOR, itemScene.getSceneBackground());
            values.put(KEY_SCENE_PLACE_ID, itemScene.getPlaceId());
            values.put(KEY_SCENE_IS_DELETED, 0);
            try {
                id = (int) db.insert(TABLE_SCENE, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        // Update History
        
        UpdateHistoryScene(id, 1, 0);
        return id;
    }
    
    /*
     * public int addScene(SceneItem itemScene) { SQLiteDatabase db =
     * this.getReadableDatabase(); ContentValues values = new ContentValues();
     * values.put(KEY_SCENE_NAME, itemScene.getSceneTitle());
     * values.put(KEY_SCENE_ICON, itemScene.getSceneIcon());
     * values.put(KEY_SCENE_COLOR, itemScene.getSceneBackground());
     * values.put(KEY_SCENE_PLACE_ID, itemScene.getPlaceId());
     * values.put(KEY_SCENE_IS_DELETED, 0); int id = (int)
     * db.insert(TABLE_SCENE, null, values); if (db != null) { db.close(); }
     * return id; }
     */
    
    private void UpdateHistoryScene(int idScene, int userId, int typeScene) {
        // TODO Auto-generated method stub
        if (idScene > 0) {
            
            HistorySceneItemAddRemove itemHisScene = new HistorySceneItemAddRemove();
            itemHisScene.setsceneId(idScene);
            itemHisScene.setUserId(userId);
            itemHisScene.setsceneType(typeScene);
            
            String mydate = String.valueOf(System.currentTimeMillis());
            itemHisScene.setDateTime(mydate);
            
            addHistorySceneAddRemove(itemHisScene);
        }
    }
    
    /*
     * Update scene
     */
    public boolean updateScene(SceneItem itemScene) {
        
        boolean vRet = false;
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_SCENE_NAME, itemScene.getSceneTitle());
            values.put(KEY_SCENE_ICON, itemScene.getSceneIcon());
            values.put(KEY_SCENE_COLOR, itemScene.getSceneBackground());
            values.put(KEY_SCENE_PLACE_ID, itemScene.getPlaceId());
            db.update(TABLE_SCENE, values, KEY_SCENE_ID + " = ? ",
                    new String[] {String.valueOf(itemScene.getSceneId())});
            vRet = true;
        } catch (SQLException e) {
            vRet = false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
        
        return vRet;
        
    }
    
    /*
     * Delete scene
     */
    
    public int deleteSceneById(SceneItem itemScene) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_SCENE_IS_DELETED, 1);
            try {
                id = db.update(
                        TABLE_SCENE,
                        values,
                        KEY_SCENE_ID + "="
                                + String.valueOf(itemScene.getSceneId()), null);
            } catch (Exception e) {
                id = -1;
            }
            
            db.close();
        }
        
        // update History
        UpdateHistoryScene(itemScene.getSceneId(), 1, 1); // type:1=Remove;
        return id;
    }
    
    public void deleteScene(SceneItem itemScene) {
        
        HistorySceneItemAddRemove itemHisScene = new HistorySceneItemAddRemove();
        // int tmp = getListDeviceToGetId();
        itemHisScene.setsceneId(itemScene.getSceneId());
        itemHisScene.setUserId(1);
        itemHisScene.setsceneType(1);
        
        String mydate_ = java.text.DateFormat.getDateTimeInstance().format(
                Calendar.getInstance().getTime());
        String mydate;
        String tmp_date = mydate_.substring(0, 3);
        Calendar calendar = Calendar.getInstance();
        int day = calendar.get(Calendar.DAY_OF_MONTH);
        mydate = tmp_date + "." + day;
        itemHisScene.setDateTime(mydate);
        
        addHistorySceneAddRemove(itemHisScene);
        // ------------------------------
        
        getWritableDatabase().delete(TABLE_SCENE, KEY_SCENE_ID + " = ?",
                new String[] {String.valueOf(itemScene.getSceneId())});
        
    }
    
    public void deleteDeviceItem(DeviceItem deviceItem) {
        getWritableDatabase().delete(TABLE_DEVICE, KEY_DEVICE_ID + " = ?",
                new String[] {String.valueOf(deviceItem.getDeviceId())});
        // update history
    }
    
    /*
     * GET LIST ALL PLACE
     */
    public ArrayList<SceneItem> getListScene() {
        ArrayList<SceneItem> listScene = new ArrayList<SceneItem>();
        String selectQuery = " SELECT " + KEY_SCENE_ID + ", " + KEY_SCENE_NAME
                + ", " + KEY_SCENE_ICON + ", " + KEY_SCENE_COLOR + ", "
                + KEY_SCENE_PLACE_ID + ", " + KEY_SCENE_IS_DELETED + " FROM "
                + TABLE_SCENE;
        selectQuery += " ORDER BY " + KEY_SCENE_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    SceneItem itemScene = new SceneItem();
                    itemScene.setSceneId(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(0)));
                    itemScene
                            .setSceneTitle(cursorSqliteAsusHelper.getString(1));
                    itemScene.setSceneIcon(cursorSqliteAsusHelper.getString(2));
                    itemScene.setSceneBackground(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(3)));
                    itemScene
                            .setScenePlace(cursorSqliteAsusHelper.getString(4));
                    listScene.add(itemScene);
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return listScene;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /*
     * Add new Wifi
     */
    public void addWifi(WifiItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_WIFI_NAME, item.getWifiName());
        values.put(KEY_WIFI_PASSWORD, item.getWifiPassword());
        values.put(KEY_WIFI_CODE, item.getWifiCode());
        values.put(KEY_WIFI_IS_DELETED, 0);
        
        db.insert(TABLE_WIFI, null, values);
        if (db != null) {
            db.close();
        }
    }
    
    /*
     * get list wifi
     */
    public ArrayList<WifiItem> getListWifi() {
        ArrayList<WifiItem> list = new ArrayList<WifiItem>();
        String selectQuery = "SELECT " + TABLE_WIFI + "." + KEY_WIFI_ID + ", "
                + TABLE_WIFI + "." + KEY_WIFI_NAME + ", " + TABLE_WIFI + "."
                + KEY_WIFI_PASSWORD + ", " + TABLE_WIFI + "." + KEY_WIFI_CODE
                + " FROM " + TABLE_WIFI;
        
        selectQuery += " WHERE " + TABLE_WIFI + "." + KEY_WIFI_IS_DELETED
                + " = 0 ";
        
        selectQuery += " ORDER BY " + TABLE_WIFI + "." + KEY_WIFI_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    WifiItem item = new WifiItem();
                    item.setWifiId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    item.setWifiName(cursorSqliteAsusHelper.getString(1));
                    item.setWifiPassword(cursorSqliteAsusHelper.getString(2));
                    item.setWifiCode(cursorSqliteAsusHelper.getString(3));
                    list.add(item);
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return list;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /*
     * Add new box
     */
    public void addBox(BoxItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_BOX_NAME, item.getBoxName());
        values.put(KEY_BOX_PASSWORD, item.getBoxPassword());
        values.put(KEY_BOX_CODE, item.getBoxCode());
        values.put(KEY_BOX_WIFI_ID, item.getWifiId());
        values.put(KEY_BOX_IS_DELETED, 0);
        
        try {
            db.insert(TABLE_BOX, null, values);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        if (db != null) {
            db.close();
        }
    }
    
    public void editNamebox(BoxItem boxItem) {
        
        ContentValues values = new ContentValues();
        values.put(KEY_BOX_NAME, boxItem.getBoxName());
        
        // values.put(KEY_BOX_ID, boxItem.getBoxId());
        getWritableDatabase().update(TABLE_BOX, values,
                KEY_BOX_ID + " = " + String.valueOf(boxItem.getBoxId()), null);
        
    }
    
    /*
     * public ArrayList<PlaceItem> sumPlaceInRoot() { ArrayList<PlaceItem>
     * listPlace = new ArrayList<PlaceItem>(); String selectQuery = "SELECT " +
     * TABLE_WIFI + "." + KEY_WIFI_ID + ", " + TABLE_WIFI + "." + KEY_WIFI_NAME
     * + ", " + TABLE_WIFI + "." + KEY_WIFI_PASSWORD + ", " + TABLE_WIFI + "." +
     * KEY_WIFI_CODE + " FROM " + TABLE_WIFI; selectQuery += " ORDER BY " +
     * KEY_SCENE_ID + " DESC";
     * 
     * SQLiteDatabase db = this.getReadableDatabase(); Cursor
     * cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
     * 
     * try { if (cursorSqliteAsusHelper.moveToFirst()) { do { PlaceItem
     * placeItem = new PlaceItem(); placeItem.setSceneId(Integer
     * .parseInt(cursorSqliteAsusHelper.getString(0))); placeItem
     * .setSceneTitle(cursorSqliteAsusHelper.getString(1));
     * 
     * } while (cursorSqliteAsusHelper.moveToNext()); } return listPlace; }
     * finally { if (cursorSqliteAsusHelper != null) {
     * cursorSqliteAsusHelper.close(); } if (db != null) { db.close(); } }
     */
    /*
     * get list box
     */
    public ArrayList<BoxItem> getListBox(int wifiId) {
        ArrayList<BoxItem> list = new ArrayList<BoxItem>();
        String selectQuery = "SELECT " + TABLE_BOX + "." + KEY_BOX_ID + ", "
                + TABLE_BOX + "." + KEY_BOX_NAME + ", " + TABLE_BOX + "."
                + KEY_BOX_PASSWORD + ", " + TABLE_BOX + "." + KEY_BOX_CODE
                + "," + TABLE_BOX + "." + KEY_BOX_WIFI_ID + ", " + TABLE_WIFI
                + "." + KEY_WIFI_NAME + ", " + " FROM " + TABLE_BOX
                
                + " LEFT OUTER JOIN " + TABLE_WIFI + " ON " + TABLE_WIFI + "."
                + KEY_WIFI_ID + " = " + TABLE_BOX + "." + KEY_BOX_WIFI_ID;
        
        selectQuery += " WHERE " + TABLE_BOX + "." + KEY_BOX_IS_DELETED
                + " = 0 ";
        selectQuery += " AND " + TABLE_BOX + "." + KEY_BOX_WIFI_ID + " = "
                + wifiId;
        
        selectQuery += " ORDER BY " + TABLE_BOX + "." + KEY_BOX_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    BoxItem item = new BoxItem();
                    item.setBoxId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    item.setBoxName(cursorSqliteAsusHelper.getString(1));
                    item.setBoxPassword(cursorSqliteAsusHelper.getString(2));
                    item.setBoxCode(cursorSqliteAsusHelper.getString(3));
                    item.setWifiId(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(4)));
                    item.setWifiName(cursorSqliteAsusHelper.getString(5));
                    list.add(item);
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return list;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /*
     * update place_id for Device with device_id
     */
    public void updatePlaceId(int deviceId, int placeId) {
        String sql = "UPDATE " + TABLE_DEVICE + " SET " + KEY_DEVICE_PLACE_ID
                + " = " + placeId + " WHERE " + KEY_DEVICE_ID + " = "
                + deviceId;
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(sql);
        
    }
    
    /*
     * Update placeId for list Device
     */
    public boolean updatePlaceIdForListDevice(ArrayList<DeviceItem> listDevice,
            int placeId) {
        
        try {
            if (listDevice.size() > 0) {
                SQLiteDatabase db = this.getReadableDatabase();
                if (db != null) {
                    String whereClause = "";
                    
                    for (DeviceItem deviceItem : listDevice) {
                        
                        whereClause += "'" + deviceItem.getDeviceId() + " ' ,";
                    }
                    whereClause = whereClause.substring(0,
                            whereClause.length() - 1);
                    whereClause = KEY_DEVICE_ID + " IN  (" + whereClause + ") ";
                    
                    ContentValues values = new ContentValues();
                    
                    values.put(KEY_DEVICE_PLACE_ID, placeId);
                    db.update(TABLE_DEVICE, values, whereClause, null);
                    
                    db.close();
                    
                    return true;
                    
                }
                
            }
        } catch (Exception e) {
            Log.e("TEST_TEST_UPDATE_SUCCESS",
                    "TEST_TEST_UPDATE_SUCCESS:" + e.toString());
        }
        
        return false;
    }
    
    /*
     * update place_id for device with old place_id
     */
    public void updatePlaceIdNew(int oldplaceId, int newplaceId) {
        String sql = "UPDATE " + TABLE_DEVICE + " SET " + KEY_DEVICE_PLACE_ID
                + " = " + newplaceId + " WHERE " + KEY_DEVICE_ID + " = "
                + oldplaceId;
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(sql);
        
    }
    
    /*
     * Delete place card
     */
    public void deletePlace(int placeId) {
        String sqlCommand = "DELETE FROM " + TABLE_PLACE + " WHERE "
                + KEY_PLACE_ID + "=" + placeId;
        SQLiteDatabase db = this.getWritableDatabase();
        
        // update history
        
        // --------------------
        db.execSQL(sqlCommand);
        
    }
    
    /*
     * update place_id for device with old place_id
     */
    
    public boolean updatePlaceToUncategory(int oldplaceId, int checkIsDelete) {
        try {
            String sql = "UPDATE " + TABLE_PLACE + " SET "
                    + KEY_PLACE_IS_DELETED + " = " + checkIsDelete + " WHERE "
                    + KEY_PLACE_ID + " = " + oldplaceId;
            SQLiteDatabase db = this.getWritableDatabase();
            db.execSQL(sql);
            
            db.close();
            // update history
            
            updateHistoryPlace(oldplaceId, 1, 1);
            
            return true;
        } catch (SQLException e) {
            Log.e("updatePlaceToUncategory",
                    "updatePlaceToUncategory " + e.toString());
        }
        return false;
        
    }
    
    // NhanNLT Add data
    
    // add data history hume user
    
    public int addDataBase_history_member_action(HistoryMemberItem item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_USER_HOME_USER_ID,
                    item.getHistoryMemberMemberId());
            values.put(KEY_HISTORY_USER_HOME_TYPE, item.getHistoryMemberType());
            values.put(KEY_HISTORY_USER_HOME_DATETIME,
                    item.getHistoryMemberDate());
            
            try {
                id = (int) db.insert(TABLE_HISTORY_USER_HOME, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    // get list history member status Come HOme/L
    
    public ArrayList<HistoryMemberItem> getListMemberHomeAction() {
        ArrayList<HistoryMemberItem> listMember = new ArrayList<HistoryMemberItem>();
        String selectQuery = " SELECT " + KEY_HISTORY_USER_HOME_ID + ", "
                + KEY_HISTORY_USER_HOME_USER_ID + ", "
                + KEY_HISTORY_USER_HOME_TYPE + ", "
                + KEY_HISTORY_USER_HOME_DATETIME + " , " + KEY_USER_NAME
                + " , " + KEY_USER_ICON + " FROM " + TABLE_HISTORY_USER_HOME
                + " , " + TABLE_USER;
        selectQuery = selectQuery + " WHERE history_user_home."
                
                + KEY_HISTORY_USER_HOME_USER_ID + " = " + " user._user_id "
                + " order by " + TABLE_HISTORY_USER_HOME + "."
                + KEY_HISTORY_USER_HOME_DATETIME + " DESC ";
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            
            Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
            
            // selectQuery_get_User_By_ID=selectQuery_get_User_By_ID+itemMember.getHistoryMemberMemberId();
            // SQLiteDatabase db2 = this.getReadableDatabase();
            // Cursor cursor_GetNameFromID =
            // db2.rawQuery(selectQuery_get_User_By_ID, null);
            try {
                if (cursorSqliteAsusHelper.moveToFirst()) {
                    do {
                        
                        HistoryMemberItem itemMember = new HistoryMemberItem();
                        itemMember.setHistoryMemberId(Integer
                                .parseInt(cursorSqliteAsusHelper.getString(0)));
                        
                        itemMember.setHistoryMemberMemberId(Integer
                                .parseInt(cursorSqliteAsusHelper.getString(1)));
                        
                        itemMember.setHistoryMemberType(Integer
                                .parseInt(cursorSqliteAsusHelper.getString(2)));
                        itemMember.setHistoryMemberDate(cursorSqliteAsusHelper
                                .getString(3));
                        
                        // ----------------
                        
                        itemMember.setHistoryMemberName(cursorSqliteAsusHelper
                                .getString(4));
                        itemMember.setHistoryMemberIcon(cursorSqliteAsusHelper
                                .getString(5));
                        
                        listMember.add(itemMember);
                        
                    } while (cursorSqliteAsusHelper.moveToNext());
                }
                return listMember;
            } finally {
                if (cursorSqliteAsusHelper != null) {
                    cursorSqliteAsusHelper.close();
                }
                if (db != null) {
                    db.close();
                }
            }
        } catch (Exception ex) {
            return listMember;
        }
    }
    
    // add history action smart
    public int addHistoryActionSmartTrigger(HistoryActionSmartTriggerDto item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_SMART_TRIGGER_WHEN_TRIGGER_ID,
                    item.getWhenId());
            values.put(KEY_HISTORY_SMART_TRIGGER_TYPE, item.getType());
            values.put(KEY_HISTORY_SMART_TRIGGER_USER_ID, item.getUserId());
            values.put(KEY_HISTORY_SMART_TRIGGER_DATETIME, item.getDate());
            try {
                id = (int) db.insert(TABLE_HISTORY_SMART_TRIGGER, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    // get list history action smart trigger
    public ArrayList<HistoryActionSmartTriggerDto> getListActionSmartTrigger() {
        ArrayList<HistoryActionSmartTriggerDto> listMember = new ArrayList<HistoryActionSmartTriggerDto>();
        String selectQuery = " SELECT " + KEY_HISTORY_SMART_TRIGGER_TYPE + ", "
                + KEY_HISTORY_SMART_TRIGGER_DATETIME + "," + KEY_USER_NAME
                + " FROM " + TABLE_HISTORY_SMART_TRIGGER + " , " + TABLE_USER
                + " WHERE " + TABLE_HISTORY_SMART_TRIGGER + "."
                + KEY_HISTORY_SMART_TRIGGER_USER_ID + " = user." + KEY_USER_ID;
        
        selectQuery += " ORDER BY " + TABLE_HISTORY_SMART_TRIGGER + "."
                + KEY_HISTORY_SMART_TRIGGER_DATETIME + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    HistoryActionSmartTriggerDto itemMember = new HistoryActionSmartTriggerDto();
                    itemMember.setType(Integer.parseInt(cursorSqliteAsusHelper
                            .getString(0)));
                    itemMember.setDate(cursorSqliteAsusHelper.getString(1));
                    itemMember.setUserName(cursorSqliteAsusHelper.getString(2));
                    
                    listMember.add(itemMember);
                    
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return listMember;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    //
    public ArrayList<DeviceItem> getDeviceByUserId() {
        ArrayList<DeviceItem> arrDeviceItem = new ArrayList<DeviceItem>();
        String selectQuery = "SELECT " + TABLE_DEVICE + "." + KEY_DEVICE_ID
                + ", " + TABLE_DEVICE + "." + KEY_DEVICE_NAME + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_USER_ID + ", " + TABLE_PLACE
                + "." + KEY_PLACE_ID + "," + TABLE_PLACE + "."
                + KEY_PLACE_CAPABILITY_TYPE + "," + TABLE_PLACE + "."
                + KEY_PLACE_NAME + "," + "COUNT (" + TABLE_DEVICE + "."
                + KEY_DEVICE_ID + ") as DEVICE_NUM" + "," + TABLE_USER + "."
                + KEY_USER_NAME;
        
        selectQuery += " FROM " + TABLE_DEVICE + " LEFT OUTER JOIN "
                + TABLE_PLACE + " ON " + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID + " LEFT OUTER JOIN " + TABLE_USER + " ON "
                + TABLE_USER + "." + KEY_USER_ID + " = " + TABLE_DEVICE + "."
                + KEY_DEVICE_USER_ID;
        selectQuery += " AND " + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED
                + " = 0 ";
        selectQuery += " GROUP BY " + TABLE_PLACE + "." + KEY_PLACE_ID;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    
                    DeviceItem deviceItem = new DeviceItem();
                    deviceItem.setDeviceId(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(0)));
                    deviceItem.setDeviceName(cursorSqliteAsusHelper
                            .getString(1));
                    deviceItem.setUserId(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(2)));
                    deviceItem.setDevicePlaceId(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(3)));
                    deviceItem.setCapability(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(4)));
                    deviceItem.setDevicePlace(cursorSqliteAsusHelper
                            .getString(5));
                    deviceItem.setCountDevice(Integer
                            .parseInt(cursorSqliteAsusHelper.getString(6)));
                    deviceItem.setUserName(cursorSqliteAsusHelper.getString(7));
                    arrDeviceItem.add(deviceItem);
                    
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            return arrDeviceItem;
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    // add User
    public int addMember(MemberItem item) {
        SQLiteDatabase db = this.getReadableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(KEY_USER_NAME, item.getMemberName());
        values.put(KEY_USER_EMAIL, item.getMemberEmail());
        values.put(KEY_USER_ICON, item.getMemberPhoto());
        values.put(KEY_USER_STATUS, item.getMemberStatus());
        
        values.put(KEY_USER_IS_DELETED, item.getMemberDeleted());
        int id = (int) db.insert(TABLE_USER, null, values);
        if (db != null) {
            db.close();
        }
        return id;
    }
    
    // get last user NhanNLT
    public String getLastUser(int id) {
        
        String selectQuery = " SELECT " + KEY_USER_ICON + " FROM " + TABLE_USER
                + " WHERE " + KEY_USER_ID + " = " + id;
        
        // selectQuery += " ORDER BY " + KEY_SCENE_ID + " DESC";
        String tmp = "";
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    tmp = cursorSqliteAsusHelper.getString(0);
                    
                } while (cursorSqliteAsusHelper.moveToNext());
            }
            
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return tmp;
    }
    
    // get List History action of Device
    
    public ArrayList<HistoryDeviceItemAddRemove> getListHistoryDeviceAddRemove() {
        ArrayList<HistoryDeviceItemAddRemove> listDevice = new ArrayList<HistoryDeviceItemAddRemove>();
        
        String sql = "SELECT "
                
                + TABLE_DEVICE + "." + KEY_DEVICE_NAME + "," + TABLE_HISTORY_DEVICE
                + "." + KEY_HISTORY_DEVICE_TYPE + "," + TABLE_HISTORY_DEVICE
                + "." + KEY_HISTORY_DEVICE_DATETIME + ","
                
                + TABLE_USER + "." + KEY_USER_NAME + " , " + TABLE_DEVICE + "."
                + KEY_DEVICE_TYPE + "," + TABLE_HISTORY_DEVICE + "."
                + KEY_HISTORY_DEVICE_TYPE + " , " + TABLE_PLACE + "."
                + KEY_PLACE_NAME + " FROM " + TABLE_HISTORY_DEVICE + " , "
                + TABLE_USER + " , " + TABLE_DEVICE + " , " + TABLE_PLACE
                + " where "
                
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_USER_ID
                + " = "
                
                + TABLE_USER + "." + KEY_USER_ID +
                
                " AND  " + TABLE_HISTORY_DEVICE + "."
                + KEY_HISTORY_DEVICE_DEVICE_ID + " = "
                
                + TABLE_DEVICE + "." + KEY_DEVICE_ID + " AND  " + TABLE_DEVICE
                + "." + KEY_DEVICE_PLACE_ID + " = "
                
                + TABLE_PLACE + "." + KEY_PLACE_ID + " order by "
                + TABLE_HISTORY_DEVICE + "." + KEY_HISTORY_DEVICE_DATETIME
                + " DESC ";
        
        sql += "";
        
        /*
         * String selectQuery = "SELECT * FROM " + TABLE_DEVICE + " WHERE " +
         * KEY_DEVICE_PLACE_ID + "=" + placeId; String selectQuery2 = "SELECT "
         * + KEY_PLACE_NAME + " FROM " + TABLE_PLACE + " WHERE " + KEY_PLACE_ID
         * + "=" + placeId;
         */
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        // Cursor curso2 = db.rawQuery(selectQuery2, null);
        try {
            if (curso.moveToFirst() /* && curso2.moveToFirst() */) {
                do {
                    HistoryDeviceItemAddRemove de = new HistoryDeviceItemAddRemove();
                    
                    de.setDeviceName(curso.getString(0).toString());
                    de.setDeviceType(Integer.parseInt(curso.getString(1)
                            .toString()));
                    de.setDateTime(curso.getString(2));
                    de.setUserName(curso.getString(3).toString());
                    de.setDeviceType(Integer.parseInt(curso.getString(4)
                            .toString()));
                    de.setDeviceTypeType(Integer.parseInt(curso.getString(5)
                            .toString()));
                    de.setPlaceName(curso.getString(6).toString());
                    listDevice.add(de);
                    
                } while (curso.moveToNext());
                
            }
            
        } catch (Exception e) {
        }
        
        return listDevice;
    }
    
    public int deleteMember(MemberItem memberItem) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_USER_IS_DELETED, 1);
            try {
                id = db.update(
                        TABLE_USER,
                        values,
                        KEY_USER_ID + "="
                                + String.valueOf(memberItem.getMemberId()),
                        null);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    public int updateMember(MemberItem memberItem) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_USER_NAME, memberItem.getMemberName());
            values.put(KEY_USER_EMAIL, memberItem.getMemberEmail());
            values.put(KEY_USER_ICON, memberItem.getMemberPhoto());
            values.put(KEY_USER_STATUS, memberItem.getMemberStatus());
            try {
                id = db.update(TABLE_USER, values, KEY_USER_ID + "="
                        + String.valueOf(memberItem.getMemberId()), null);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    public ArrayList<MemberItem> getListMember() {
        ArrayList<MemberItem> listmember = new ArrayList<MemberItem>();
        String selectQuery = "SELECT * FROM " + TABLE_USER;
        selectQuery += " WHERE " + TABLE_USER + "." + KEY_USER_IS_DELETED
                + " = " + 0;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    MemberItem member = new MemberItem();
                    member.setMemberId(cursor.getInt(0));
                    member.setMemberName(cursor.getString(1));
                    member.setMemberEmail(cursor.getString(2));
                    member.setMemberPhoto(cursor.getString(3));
                    member.setMemberStatus(cursor.getInt(4));
                    member.setMemberDeleted(Integer.parseInt(cursor
                            .getString(5)));
                    listmember.add(member);
                } while (cursor.moveToNext());
            }
            return listmember;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    // add data to History Device add/remove
    public int addDataToHistoryDevice(HistoryDeviceItemAddRemove item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_DEVICE_DEVICE_ID, item.getDeviceId());
            values.put(KEY_HISTORY_DEVICE_USER_ID, item.getUserId());
            values.put(KEY_HISTORY_DEVICE_TYPE, item.getDeviceType());
            
            values.put(KEY_HISTORY_DEVICE_DATETIME, item.getDateTime());
            
            try {
                id = (int) db.insert(TABLE_HISTORY_DEVICE, null, values);
                
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    // get id new device
    public int getListDeviceToGetId() {
        ArrayList<DeviceItem> list = new ArrayList<DeviceItem>();
        String selectQuery = "SELECT * from Device";
        int a = 0;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    DeviceItem device = new DeviceItem();
                    device.setDeviceId(Integer.parseInt(cursor.getString(0)));
                    a = device.getDeviceId();
                    device.setDeviceName(cursor.getString(1));
                    device.setDeviceIcon(cursor.getString(2));
                    device.setDeviceType(Integer.parseInt(cursor.getString(3)));
                    list.add(device);
                    
                } while (cursor.moveToNext());
            }
            
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return a;
    }
    
    // add history place new/remove
    public int addHistoryPlaceAddRemove(HistoryPlaceItemAddRemove item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_PLACE_PLACE_ID, item.getPlacePlaceId());
            values.put(KEY_HISTORY_PLACE_TYPE, item.getPlaceType());
            values.put(KEY_HISTORY_PLACE_USER_ID, item.getUserId());
            values.put(KEY_HISTORY_PLACE_DATETIME, item.getDateTime());
            try {
                id = (int) db.insert(TABLE_HISTORY_PLACE, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    // get array list History Place
    // getListHistoryPlaceAddRemove
    public ArrayList<HistoryPlaceItemAddRemove> getListHistoryPlaceAddRemove() {
        ArrayList<HistoryPlaceItemAddRemove> listDevice = new ArrayList<HistoryPlaceItemAddRemove>();
        
        String sql = "SELECT "
                
                + TABLE_USER + "." + KEY_USER_NAME + "," + TABLE_PLACE + "."
                + KEY_PLACE_NAME + " , " + TABLE_HISTORY_PLACE + "."
                + KEY_HISTORY_PLACE_TYPE + "," + TABLE_HISTORY_PLACE + "."
                + KEY_HISTORY_PLACE_DATETIME + " FROM " + TABLE_HISTORY_PLACE
                + " , " + TABLE_USER + " , " + TABLE_PLACE + " where "
                
                + TABLE_HISTORY_PLACE + "." + KEY_HISTORY_PLACE_PLACE_ID
                + " = "
                
                + TABLE_PLACE + "." + KEY_PLACE_ID +
                
                " AND  " + TABLE_HISTORY_PLACE + "."
                + KEY_HISTORY_PLACE_USER_ID + " = "
                
                + TABLE_USER + "." + KEY_USER_ID + " order by "
                + TABLE_HISTORY_PLACE + "." + KEY_HISTORY_PLACE_DATETIME
                + " DESC ";
        
        sql += "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        // Cursor curso2 = db.rawQuery(selectQuery2, null);
        try {
            if (curso.moveToFirst() /* && curso2.moveToFirst() */) {
                do {
                    HistoryPlaceItemAddRemove de = new HistoryPlaceItemAddRemove();
                    
                    de.setUserName(curso.getString(0).toString());
                    de.setPlaceName(curso.getString(1).toString());
                    de.setPlaceType(Integer.parseInt(curso.getString(2)));
                    de.setDateTime(curso.getString(3));
                    listDevice.add(de);
                    
                } while (curso.moveToNext());
                
            }
            
        } catch (Exception e) {
        }
        
        return listDevice;
    }
    
    // get List History Scene
    public ArrayList<HistorySceneItemAddRemove> getListHistorySceneAddRemove() {
        ArrayList<HistorySceneItemAddRemove> listDevice = new ArrayList<HistorySceneItemAddRemove>();
        
        String sql = "SELECT " + TABLE_HISTORY_SCENE + "."
                + KEY_HISTORY_SCENE_TYPE + "," + TABLE_USER + "."
                + KEY_USER_NAME + "," + TABLE_SCENE + "." + KEY_SCENE_NAME
                + "," + TABLE_PLACE + "." + KEY_PLACE_NAME + " , "
                + TABLE_HISTORY_SCENE + "." + KEY_HISTORY_SCENE_DATETIME
                + " FROM " + TABLE_HISTORY_SCENE + " , " + TABLE_USER + " , "
                + TABLE_SCENE + " , " + TABLE_PLACE +
                
                " where "
                
                + TABLE_HISTORY_SCENE + "." + KEY_HISTORY_SCENE_USER_ID + " = "
                
                + TABLE_USER + "." + KEY_USER_ID +
                
                " AND  " + TABLE_HISTORY_SCENE + "."
                + KEY_HISTORY_SCENE_SCENE_ID + " = " + TABLE_SCENE + "."
                + KEY_SCENE_ID + " AND " + TABLE_PLACE + "." + KEY_PLACE_ID
                + " = " + TABLE_SCENE + "." + KEY_SCENE_PLACE_ID + " order by "
                + TABLE_HISTORY_SCENE + "." + KEY_HISTORY_SCENE_DATETIME
                + " DESC ";
        
        ;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        // Cursor curso2 = db.rawQuery(selectQuery2, null);
        try {
            if (curso.moveToFirst() /* && curso2.moveToFirst() */) {
                do {
                    HistorySceneItemAddRemove de = new HistorySceneItemAddRemove();
                    
                    de.setsceneType(Integer.parseInt(curso.getString(0)
                            .toString()));
                    de.setUserName(curso.getString(1).toString());
                    de.setsceneName(curso.getString(2).toString());
                    de.setPlaceName(curso.getString(3).toString());
                    de.setDateTime(curso.getString(4).toString());
                    
                    listDevice.add(de);
                    
                } while (curso.moveToNext());
                
            }
            
        } catch (Exception e) {
        }
        
        return listDevice;
    }
    
    // add history scene new/remove
    public int addHistorySceneAddRemove(HistorySceneItemAddRemove item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_SCENE_TYPE, item.getsceneType());
            values.put(KEY_HISTORY_SCENE_SCENE_ID, item.getsceneId());
            values.put(KEY_HISTORY_SCENE_USER_ID, item.getUserId());
            values.put(KEY_HISTORY_SCENE_DATETIME, item.getDateTime());
            try {
                id = (int) db.insert(TABLE_HISTORY_SCENE, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
        
    }
    
    public ArrayList<SmartTriggerItem> getListSmartTrigger(
            DeviceItem idItemDeviceTrigger) {
        ArrayList<SmartTriggerItem> mLst = new ArrayList<SmartTriggerItem>();
        String sqlSelect = "SELECT " + TABLE_ST_WHEN + "." + KEY_ST_WHEN_VALUE
                + ", " + TABLE_ST_WHEN + "." + KEY_ST_WHEN_TYPE + ", "
                + TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_VALUE + ", "
                + TABLE_ST_TRIGGER + "." + KEY_ST_TRIGGER_ACTION + ", "
                + TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_NAME + ", "
                + TABLE_ST_WHEN_TRIGGER + "." + KEY_ST_WHEN_TRIGGER_WHEN_ID
                + ", " + TABLE_ST_WHEN_TRIGGER + "."
                + KEY_ST_WHEN_TRIGGER_TRIGGER_ID + ", " + TABLE_ST_TRIGGER
                + "." + KEY_ST_TRIGGER_DEVICE_ID + ", " + TABLE_ST_WHEN_TRIGGER
                + "." + KEY_ST_WHEN_TRIGGER_ONOFF + " FROM " + TABLE_ST_WHEN
                + ", " + TABLE_ST_TRIGGER + ", " + TABLE_ST_WHEN_TRIGGER
                + " WHERE " + TABLE_ST_WHEN_TRIGGER + "."
                + KEY_ST_WHEN_TRIGGER_WHEN_ID + "=" + TABLE_ST_WHEN + "."
                + KEY_ST_WHEN_ID + " AND " + TABLE_ST_WHEN_TRIGGER + "."
                + KEY_ST_WHEN_TRIGGER_TRIGGER_ID + "=" + TABLE_ST_TRIGGER + "."
                + KEY_ST_TRIGGER_ID + " AND " + TABLE_ST_TRIGGER + "."
                + KEY_ST_TRIGGER_DEVICE_ID + "="
                + idItemDeviceTrigger.getDeviceId();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(sqlSelect, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    SmartTriggerItem item = new SmartTriggerItem();
                    item.setWhenDeviceValues(cursor.getString(0));
                    item.setWhenDeviceType(cursor.getInt(1));
                    item.setTriggerDeveceValues(cursor.getString(2));
                    item.setTriggerDeviceType(cursor.getInt(3));
                    item.setSmarttriggername(cursor.getString(4));
                    item.setWhenId(cursor.getInt(5));
                    item.setTriggerId(cursor.getInt(6));
                    item.setTriggerDeviceId(7);
                    item.setIconControl(cursor.getInt(8));
                } while (cursor.moveToNext());
            }
            return mLst;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
    public void updateHistoryDevice(int id, int userId, int type_history_device) {
        if (id > 0) {
            
            HistoryDeviceItemAddRemove itemHisDevice = new HistoryDeviceItemAddRemove();
            // int tmp = getListDeviceToGetId();
            
            itemHisDevice.setDeviceId(id);
            itemHisDevice.setUserId(userId);
            itemHisDevice.setDeviceType(type_history_device);
            String mydate = String.valueOf(System.currentTimeMillis());
            itemHisDevice.setDateTime(mydate);
            addDataToHistoryDevice(itemHisDevice);
        }
    }
    
    // add history change place of device
    public int addHistoryChangePlaceOfDevice(HistoryChangePlaceOfDevice item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID,
                    item.getDeviceId());
            values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID,
                    item.getPlaceId());
            values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID,
                    item.getMemberChangeId());
            values.put(KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DATETIME,
                    item.getDateTimeAdd());
            try {
                id = (int) db.insert(TABLE_HISTORY_MOVE_PLACE_OF_DEVICE, null,
                        values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    // add history rename place or change capability
    public int addHistoryRenameOrChangeCapabilityOfPlace(
            HistoryRenamePlaceOrChangeCapability item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID,
                    item.getPlaceId());
            values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID,
                    item.getMemberId());
            values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM,
                    item.getRenameFrom());
            values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO,
                    item.getRenameTo());
            values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM,
                    item.getCapabilityFrom());
            values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO,
                    item.getCapabilityTo());
            values.put(KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME,
                    item.getTimeAdd());
            try {
                id = (int) db.insert(TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE,
                        null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
    // get list HistoryWarningSensorsTrigger
    public ArrayList<HistoryWarningSensorsTrigger> getListHistoryWarningSensorsTrigger() {
        ArrayList<HistoryWarningSensorsTrigger> listDevice = new ArrayList<HistoryWarningSensorsTrigger>();
        
        String sql = "SELECT " + TABLE_HISTORY_WARNING + "."
                + KEY_HISTORY_WARNING_ID + " , " + TABLE_DEVICE + "."
                + KEY_DEVICE_ID + "," + TABLE_DEVICE + "." + KEY_DEVICE_NAME
                + " , " + TABLE_HISTORY_WARNING + "."
                + KEY_HISTORY_WARNING_DATETIME + " FROM "
                + TABLE_HISTORY_WARNING + " , " + TABLE_DEVICE + " where "
                + TABLE_DEVICE + "." + KEY_DEVICE_ID + " = "
                + TABLE_HISTORY_WARNING + "." + KEY_HISTORY_WARNING_DEVICE_ID;
        sql += "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        try {
            if (curso.moveToFirst()) {
                do {
                    HistoryWarningSensorsTrigger node = new HistoryWarningSensorsTrigger();
                    node.setHistoryWarningSensorsId(curso.getInt(0));
                    node.setSensorName(curso.getString(2));
                    node.setDateTimeAdd(curso.getString(3));
                    listDevice.add(node);
                } while (curso.moveToNext());
            }
        } catch (Exception e) {
        }
        return listDevice;
    }
    
    // get list HistoryChangePlaceOfDevice
    public ArrayList<HistoryChangePlaceOfDevice> getListHistoryChangePlaceOfDevice() {
        ArrayList<HistoryChangePlaceOfDevice> listhistory = new ArrayList<HistoryChangePlaceOfDevice>();
        
        String sql = "SELECT " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID + " , "
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_DEVICE_ID + ","
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID + " , "
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID + " , "
                + TABLE_PLACE + "." + KEY_PLACE_NAME + " , " + TABLE_USER + "."
                + KEY_USER_NAME + " , " + TABLE_DEVICE + "." + KEY_DEVICE_NAME
                
                + " FROM " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + " , "
                + TABLE_USER + " , " + TABLE_PLACE + " , " + TABLE_DEVICE
                + " where " + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_USER_ID + " = " + TABLE_USER
                + "." + KEY_USER_ID + " and "
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_PLACE_ID + " = "
                + TABLE_PLACE + "." + KEY_PLACE_ID + " and " + TABLE_DEVICE
                + "." + KEY_DEVICE_ID + "="
                + TABLE_HISTORY_MOVE_PLACE_OF_DEVICE + "."
                + KEY_HISTORY_MOVE_PLACE_OF_DEVICE_ID;
        sql += "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        if (curso.moveToFirst()) {
            do {
                HistoryChangePlaceOfDevice item = new HistoryChangePlaceOfDevice();
                item.setHistoryChangePlaceOfDeviceId(curso.getInt(0));
                item.setDeviceId(curso.getInt(1));
                item.setPlaceId(curso.getInt(2));
                item.setMemberChangeId(curso.getInt(3));
                item.setPlaceName(curso.getString(4));
                item.setMemberChange(curso.getString(5));
                item.setDeviceName(curso.getString(6));
                listhistory.add(item);
            } while (curso.moveToNext());
        }
        return listhistory;
    }
    
    // get list HistoryRenamePlaceOrChangeCapability
    public ArrayList<HistoryRenamePlaceOrChangeCapability>
            getListHistoryRenamePlaceOrChangeCapability() {
        ArrayList<HistoryRenamePlaceOrChangeCapability> listHistory = new ArrayList<HistoryRenamePlaceOrChangeCapability>();
        String sql = "SELECT " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_PLACE_ID + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_FROM + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_RENAME_TO + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_FROM + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_CAPABILITY_TO + " , "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_DATETIME + " , "
                + TABLE_USER + "." + KEY_USER_NAME + " , " + TABLE_PLACE + "."
                + KEY_PLACE_NAME
                
                + " FROM " + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + " , "
                + TABLE_USER + " , " + TABLE_PLACE + " where "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_ID + " = " + TABLE_PLACE
                + "." + KEY_PLACE_ID + " and "
                + TABLE_HISTORY_NAME_CAPABILITY_OF_PLACE + "."
                + KEY_HISTORY_NAME_CAPABILITY_OF_PLACE_USER_ID + " = "
                + TABLE_USER + "." + KEY_USER_ID;
        sql += "";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor curso = db.rawQuery(sql, null);
        try {
            if (curso.moveToFirst()) {
                do {
                    HistoryRenamePlaceOrChangeCapability item = new HistoryRenamePlaceOrChangeCapability();
                    item.setHistoryRenamePlaceOrChangeCapabilityID(curso
                            .getInt(0));
                    item.setPlaceId(curso.getInt(1));
                    item.setMemberId(curso.getInt(2));
                    item.setRenameFrom(curso.getString(3));
                    item.setRenameTo(curso.getString(4));
                    item.setCapabilityFrom(curso.getInt(5));
                    item.setCapabilityTo(curso.getInt(6));
                    item.setTimeAdd(curso.getString(7));
                    item.setMemberName(curso.getString(8));
                    item.setPlaceName(curso.getString(9));
                    listHistory.add(item);
                } while (curso.moveToNext());
            }
        } catch (Exception e) {
        }
        return listHistory;
    }
    
    public void updateHistoryPlace(int idPlace, int userId, int typeHistoryPlace) {
        
        if (idPlace > 0) {
            HistoryPlaceItemAddRemove item_history = new HistoryPlaceItemAddRemove();
            item_history.setPlacePlaceId(idPlace);
            item_history.setPlaceType(typeHistoryPlace); // its up to action.
            item_history.setUserId(userId); // get current user!
            String mydate = String.valueOf(System.currentTimeMillis());
            item_history.setDateTime(mydate);
            
            addHistoryPlaceAddRemove(item_history);
        }
    }
    
    public void updateHistorySmartTrigger(int whenTriggerId, int userId,
            int typeHistorySmartTrigger) {
        
        if (whenTriggerId > 0) {
            HistoryActionSmartTriggerDto item_history = new HistoryActionSmartTriggerDto();
            item_history.setWhenId(whenTriggerId);
            item_history.setType(typeHistorySmartTrigger); // its up to action.
            item_history.setUserId(userId); // get current user!
            String mydate = String.valueOf(System.currentTimeMillis());
            item_history.setDate(mydate);
            addHistoryActionSmartTrigger(item_history);
        }
    }
    
    /*
     * letq use case 7-1-1 ,7-1-2
     */
    public ArrayList<HistoryWifimatchHome> getListWifiHistory() {
        SQLiteDatabase sql = this.getReadableDatabase();
        ArrayList<HistoryWifimatchHome> listWifiHistory = new ArrayList<HistoryWifimatchHome>();
        String querysql = " SELECT " + TABLE_BOX + "." + KEY_BOX_NAME + ","
                + TABLE_HISTORY_BOX_WIFI + "." + KEY_HISTORY_BOX_WIFI_DATETIME
                + "," + TABLE_HISTORY_BOX_WIFI + "."
                + KEY_HISTORY_BOX_WIFI_WIFI_ID + "," + TABLE_USER + "."
                + KEY_USER_ICON + "," + TABLE_USER + "." + KEY_USER_NAME + ","
                + TABLE_WIFI + "." + KEY_WIFI_NAME + " FROM "
                
                + TABLE_HISTORY_BOX_WIFI + "," + TABLE_BOX + "," + TABLE_USER
                + "," + TABLE_WIFI + " WHERE "
                
                + TABLE_HISTORY_BOX_WIFI + "." + KEY_HISTORY_BOX_WIFI_WIFI_ID
                + "=" + TABLE_BOX + "." + KEY_BOX_WIFI_ID + " AND "
                + TABLE_WIFI + "." + KEY_WIFI_ID + "=" + TABLE_BOX + "."
                + KEY_BOX_WIFI_ID + " AND " + TABLE_HISTORY_BOX_WIFI + "."
                + KEY_HISTORY_BOX_WIFI_USER_ID + "=" + TABLE_USER + "."
                + KEY_USER_ID;
        Cursor cusor = sql.rawQuery(querysql, null);
        
        try {
            if (cusor.moveToFirst()) {
                do {
                    HistoryWifimatchHome historywifi = new HistoryWifimatchHome();
                    
                    historywifi.setmBoxNameHistory(cusor.getString(0)
                            .toString());
                    historywifi.setmDateTime(cusor.getString(1));
                    historywifi
                            .setmWifiId(Integer.parseInt(cusor.getString(2)));
                    historywifi.setmIconUserHistory(cusor.getString(3));
                    historywifi.setmMemberName(cusor.getString(4));
                    historywifi.setmNameWifiConnection(cusor.getString(5));
                    listWifiHistory.add(historywifi);
                    
                } while (cusor.moveToNext());
            }
            
        } catch (Exception e) {
            
        }
        sql.close();
        
        return listWifiHistory;
        
    }
    
    /*
     * get list action of member join
     */
    public ArrayList<HistoryMemberActionJoin> getListActionofMemberJOIN() {
        String sql = " SELECT " + TABLE_USER + "." + KEY_USER_ICON + ","
                + TABLE_USER + "." + KEY_USER_NAME + "," + TABLE_BOX + "."
                + KEY_BOX_NAME + "," + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_DATETIME
                
                + " FROM " + TABLE_HISTORY_USER_ACTION + "," + TABLE_USER + ","
                + TABLE_BOX
                
                + " WHERE " + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_TYPE + " = " + "'JOIN'" + " AND "
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_USER_ID + "=" + TABLE_USER + "."
                + KEY_USER_ID + " AND " + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_BOX_ID + "=" + TABLE_BOX + "."
                + KEY_BOX_ID;
        ArrayList<HistoryMemberActionJoin> listActionJoin = new ArrayList<HistoryMemberActionJoin>();
        SQLiteDatabase sqldatabase = this.getWritableDatabase();
        Cursor querysql = sqldatabase.rawQuery(sql, null);
        
        try {
            if (querysql.moveToFirst()) {
                do {
                    HistoryMemberActionJoin recordHistoryaction = new HistoryMemberActionJoin();
                    
                    recordHistoryaction.setmIconHistoryMember(querysql
                            .getString(0));
                    recordHistoryaction.setmMemberNameHistory(querysql
                            .getString(1));
                    recordHistoryaction.setmBoxNameHistoryMember(querysql
                            .getString(2));
                    recordHistoryaction.setmDateTimeHistoryMember(querysql
                            .getString(3));
                    recordHistoryaction.setmTitleMemberHistory("A new member");
                    listActionJoin.add(recordHistoryaction);
                    
                } while (querysql.moveToNext());
            }
            
        } catch (Exception e) {
            // TODO: handle exception
        }
        return listActionJoin;
    }
    
    /*
     * add data for history_action_member
     */
    public void addDataforActionMember(int _history_user_action_id,
            int _history_user_action_user_id, String _history_user_action_type,
            int _history_user_action_by_user_id,
            int _history_user_action_box_id,
            String _history_user_action_datetime) {
        ContentValues values = new ContentValues();
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            values.put("_history_user_action_id", _history_user_action_id);
            values.put("_history_user_action_user_id",
                    _history_user_action_user_id);
            values.put("_history_user_action_type", _history_user_action_type);
            values.put("_history_user_action_by_user_id",
                    _history_user_action_by_user_id);
            values.put("_history_user_action_box_id",
                    _history_user_action_box_id);
            values.put("_history_user_action_datetime",
                    _history_user_action_datetime);
            
        }
        try {
            db.insert(TABLE_HISTORY_USER_ACTION, null, values);
        } catch (Exception e) {
            Log.e("can't insert data ", e.toString());
        }
        db.close();
        
    }
    
    /*
     * get List Data for memberHistory Removed
     */
    public ArrayList<HistoryMemberActionRemoved> getListActionofMemberREMOVED() {
        String sql1 = " SELECT " + TABLE_USER + "." + KEY_USER_ICON + ","
                + TABLE_USER + "." + KEY_USER_NAME + ","
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_DATETIME
                
                + " FROM " + TABLE_HISTORY_USER_ACTION + "," + TABLE_USER
                
                + " WHERE " + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_TYPE + " = " + "'REMOVE'" + " AND "
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_USER_ID + "=" + TABLE_USER + "."
                + KEY_USER_ID;
        
        String sql2 = " SELECT " + TABLE_USER + "." + KEY_USER_NAME + ","
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_BY_USER_ID
                
                + " FROM " + TABLE_HISTORY_USER_ACTION + "," + TABLE_USER
                
                + " WHERE " + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_TYPE + " = " + "'REMOVE'" + " AND "
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_BY_USER_ID + "=" + TABLE_USER + "."
                + KEY_USER_ID;
        ArrayList<HistoryMemberActionRemoved> listActionRemoved = new ArrayList<HistoryMemberActionRemoved>();
        SQLiteDatabase sqldatabase = this.getWritableDatabase();
        Cursor querysql1 = sqldatabase.rawQuery(sql1, null);
        Cursor querysql2 = sqldatabase.rawQuery(sql2, null);
        try {
            if (querysql1.moveToFirst() && querysql2.moveToFirst()) {
                do {
                    HistoryMemberActionRemoved recordHistoryaction = new HistoryMemberActionRemoved();
                    recordHistoryaction.setmIconMemberHistory(querysql1
                            .getString(0));
                    recordHistoryaction.setmMemberHistory(querysql1
                            .getString(1));
                    recordHistoryaction.setmDateTimeHistory(querysql1
                            .getString(2));
                    recordHistoryaction.setmByMemberHistory(querysql2
                            .getString(0));
                    listActionRemoved.add(recordHistoryaction);
                    
                } while (querysql1.moveToNext() && querysql2.moveToNext());
            }
            
        } catch (Exception e) {
            // TODO: handle exception
        }
        return listActionRemoved;
    }
    
    /*
     * getData for history Member SignOut
     */
    public ArrayList<HistoryMemberActionSignout> getListMemberSignoutHistory() {
        String sql = " SELECT " + TABLE_USER + "." + KEY_USER_ICON + ","
                + TABLE_USER + "." + KEY_USER_NAME + ","
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_DATETIME
                
                + " FROM " + TABLE_HISTORY_USER_ACTION + "," + TABLE_USER
                
                + " WHERE "
                
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_TYPE + " = " + "'SIGNOUT'" + " AND "
                + TABLE_HISTORY_USER_ACTION + "."
                + KEY_HISTORY_USER_ACTION_USER_ID + "=" + TABLE_USER + "."
                + KEY_USER_ID;
        
        ArrayList<HistoryMemberActionSignout> ListActionSignout = new ArrayList<HistoryMemberActionSignout>();
        SQLiteDatabase SQL = this.getReadableDatabase();
        Cursor cursor = SQL.rawQuery(sql, null);
        try {
            
            if (cursor.moveToFirst()) {
                do {
                    HistoryMemberActionSignout recordActionSignout = new HistoryMemberActionSignout();
                    recordActionSignout.setmIconMemberHistory(cursor
                            .getString(0));
                    recordActionSignout.setmNameMemberHistory(cursor
                            .getString(1));
                    recordActionSignout
                            .setmNameMemberAction("sign out Home Box");
                    recordActionSignout
                            .setmDateTimeHistory(cursor.getString(2));
                    ListActionSignout.add(recordActionSignout);
                    
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
        }
        return ListActionSignout;
        
    }
    
    /*
     * add data for wifi match box History
     */
    
    public void addDataWifiMatchBox(int _history_box_wifi_id,
            int _history_box_wifi_box_id, int _history_box_wifi_wifi_id,
            int _history_box_wifi_user_id, int _history_box_wifi_type,
            String _history_box_wifi_status, String _history_box_wifi_datetime) {
        ContentValues values = new ContentValues();
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            values.put("_history_box_wifi_id", _history_box_wifi_id);
            values.put("_history_box_wifi_box_id", _history_box_wifi_box_id);
            values.put("_history_box_wifi_wifi_id", _history_box_wifi_wifi_id);
            values.put("_history_box_wifi_user_id", _history_box_wifi_user_id);
            values.put("_history_box_wifi_type", _history_box_wifi_type);
            values.put("_history_box_wifi_status", _history_box_wifi_status);
            values.put("_history_box_wifi_datetime", _history_box_wifi_datetime);
            
        }
        try {
            db.insert(TABLE_HISTORY_BOX_WIFI, null, values);
        } catch (Exception e) {
            Log.e("can't insert data ", e.toString());
        }
        db.close();
        
    }
    
    // add history Warning sensors trigger
    public int addHistoryWarningSensorsTrigger(HistoryWarningSensorsTrigger item) {
        int id = -1;
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null) {
            ContentValues values = new ContentValues();
            values.put(KEY_HISTORY_WARNING_DEVICE_ID, item.getSensorId());
            values.put(KEY_HISTORY_WARNING_DATETIME, item.getDateTimeAdd());
            try {
                id = (int) db.insert(TABLE_HISTORY_WARNING, null, values);
            } catch (Exception e) {
                id = -1;
            }
            db.close();
        }
        return id;
    }
    
}
